-------------------------------------------------------------------------------------------------------------------------------------------
-- StoredProcedure: QuotePolicyDataMigration
-------------------------------------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Columbus.QuotePolicyDataMigration') AND type in (N'P', N'PC'))
	DROP PROCEDURE Columbus.QuotePolicyDataMigration
GO

CREATE PROCEDURE Columbus.QuotePolicyDataMigration
(
	@quoteIdOffSet INT,
	@pandiBusinessClassOffset INT,
	@defenceBusinessClassIdOffset INT,
	@crewFactor INT,
	@officerIdOffset INT,
	@quoteId INT,
	@importCoverageGroups BIT,
	@importCoverages BIT,
	@importRules BIT,
	@importClauses BIT,
	@importPolicyItems BIT,
	@importJointEntrants BIT,
	@importCoAssureds BIT,
	@importMortgagees BIT,
	@importGxlCategories BIT,
	@importPremiums BIT,
	@importBrokerages BIT,
	@importPremiumHistories BIT,
	@importCrews BIT,
	@importOfficers BIT,
	@importPolicyVersionActions BIT,
	@importBilling BIT
)
AS
BEGIN
	DECLARE @maxPolicyNumber INT

	DECLARE @importedQuotes TABLE
	(
		QuoteId						int,
		VersionNo					smallint,
		ClubId						int,
		CreatedBy					int,
		CreatedOn					datetime,
		UpdatedBy					int,
		UpdatedOn					datetime,
		Columbus_RenewalPolicyId	int,
		CoverNo						int,
		MainMemberId				int,
		PRIMARY KEY CLUSTERED (QuoteId, VersionNo)
	)	
	
	SELECT @maxPolicyNumber=MAX(Number) FROM Columbus.Policy
	
	
---------------------------------------------------------------------------------------------------	
--  Import Policy
---------------------------------------------------------------------------------------------------	
	
	EXEC Columbus.WriteLine 'Importing Quote Policies'
	
	;WITH latestQuote AS
	(
		SELECT QuoteId, MAX(VersionNo) VersionNo
		FROM dbo.UWQuote
		WHERE (@quoteId IS NULL OR QuoteId=@quoteId)
		GROUP BY QuoteId
	)
	INSERT INTO @importedQuotes
	SELECT  
		Q.QuoteId,
		Q.VersionNo,
		Q.ClubId,
		Q.CreatedBy,
		Q.CreatedOn,
		Q.UpdatedBy,
		Q.UpdatedOn,
		CASE WHEN P.Id IS NULL 
			THEN Q.Columbus_RenewalPolicyId
			ELSE NULL
        END,
		COALESCE(C.CoverNo,@maxPolicyNumber + ROW_NUMBER() OVER (ORDER BY Q.QuoteId)),
		Q.MemberId
	FROM dbo.UWQuote Q
		JOIN latestQuote L ON Q.QuoteId=L.QuoteId AND Q.VersionNo=L.VersionNo
		LEFT JOIN Columbus.Policy P ON Q.Columbus_RenewalPolicyId = P.RenewedFromPolicy_Id
        LEFT JOIN dbo.UWCover C ON P.Id IS NULL AND Q.Columbus_RenewalPolicyId=C.CoverId      
WHERE Q.StatusId != 'B'
	
	INSERT INTO Columbus.Policy
	(
		Id,
		Club_Id,
		Number,
		RenewedFromPolicy_Id,
		CreatedBy,
		CreatedOn,
		UpdatedBy,
		UpdatedOn		
	)
	SELECT
		QuoteId+@quoteIdOffSet,
		ClubId,
		CoverNo,
		Columbus_RenewalPolicyId,
		CreatedBy,
		CreatedOn,
		UpdatedBy,
		UpdatedOn				
	FROM @importedQuotes
	
	
	
---------------------------------------------------------------------------------------------------
--  Create Business Types for Policies with Multiple Types
---------------------------------------------------------------------------------------------------	
	EXEC Columbus.WriteLine  'Splitting Quote Business Types'

	DECLARE @multipleBusinessTypes TABLE
	(
		QuoteId			dbo.udtMasterId,
		VersionNo		smallint,
		BusinessType	nvarchar(75),
		BusinessTypeId	dbo.udtLookup
	)	

	;WITH QuotesWithMultipleBusinessTypes AS
	(
		SELECT  QL.QuoteId,QL.VersionNo
		FROM    dbo.UWQuoteClassBusinessLink QL
		JOIN	@importedQuotes Q ON QL.QuoteId=Q.QuoteId AND QL.VersionNo=Q.VersionNo
		JOIN    GLClassTypeBusinessType GL ON QL.ClassTypeBusinessTypeId = GL.ClassTypeBusinessTypeId
		GROUP BY	QL.QuoteId,QL.VersionNo
		HAVING count(DISTINCT GL.BusinessTypeId)>1
	)
	,QuoteBusinessTypeCombinations AS	
	(
		SELECT DISTINCT QL.QuoteId,QL.VersionNo,BT.LookupName BusinessType, BT.LookupId BusinessTypeId
		FROM    dbo.UWQuoteClassBusinessLink QL
		JOIN	QuotesWithMultipleBusinessTypes QM ON QL.QuoteId=QM.QuoteId AND QL.VersionNo=QM.VersionNo
		JOIN GLClassTypeBusinessType GL ON QL.ClassTypeBusinessTypeId = GL.ClassTypeBusinessTypeId
		JOIN dbo.GLLookup BT ON BT.LookupCategoryId = 'BUSINESSTYPE' AND GL.BusinessTypeId = BT.LookupId
	)
	-- see http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

	INSERT INTO @multipleBusinessTypes
	(
		QuoteId,
		VersionNo,
		BusinessType,
		BusinessTypeId
	)		
	SELECT  DISTINCT
			Main.QuoteId,
			Main.VersionNo,
			left(Main.BusinessTypes, len(Main.BusinessTypes)-1) AS BusinessType,
			left(Main.BusinessTypeIds, len(Main.BusinessTypeIds)-1) AS BusinessTypeId
	FROM
		(
			SELECT DISTINCT		
 					 BT2.QuoteId,
					 BT2.VersionNo,
					 (
						SELECT BT1.BusinessType+',' AS [text()]
						FROM QuoteBusinessTypeCombinations BT1
						WHERE BT1.QuoteId=BT2.QuoteId AND BT1.VersionNo = BT2.VersionNo
						ORDER BY BT1.QuoteId,BT1.VersionNo,BT1.BusinessType
						FOR XML PATH('')
					 ) BusinessTypes,
					 (
						SELECT BT1.BusinessTypeId+'_' AS [text()]
						FROM QuoteBusinessTypeCombinations BT1
						WHERE BT1.QuoteId=BT2.QuoteId AND BT1.VersionNo = BT2.VersionNo
						ORDER BY BT1.QuoteId,BT1.VersionNo,BT1.BusinessType
						FOR XML PATH('')
					 ) BusinessTypeIds
			FROM QuoteBusinessTypeCombinations BT2
		) Main
		
	INSERT INTO dbo.GLLookup 
	(
		LookupCategoryId,
		LookupId,
		LookupRef,
		CultureCode,
		LookupName,
		DefaultYN,
		SortOrder,
		SystemYN,
		DeactiveYN,
		CreatedBy,
		CreatedOn
	)
	SELECT DISTINCT
		'BUSINESSTYPE',
		BusinessTypeId,
		BusinessTypeId,
		'en-GB',
		BusinessType,
		'N',
		999,
		'Y',
		'Y',
		1,
		getdate()
	FROM @multipleBusinessTypes		

---------------------------------------------------------------------------------------------------
--  Create Business Classes for Policies with Multiple Classes
---------------------------------------------------------------------------------------------------	
	EXEC Columbus.WriteLine  'Splitting Quote Business Classes'
	
	DECLARE @multipleBusinessClasses TABLE
	(
		QuoteId			dbo.udtMasterId,
		VersionNo		smallint,
		BusinessClass	nvarchar(75),
		BusinessClassId	dbo.udtLookup
	)


	;WITH QuotesWithMultipleBusinessClasses AS
	(
		SELECT  QL.QuoteId,QL.VersionNo
		FROM    dbo.UWQuoteClassBusinessLink QL
		JOIN	@importedQuotes Q ON QL.QuoteId=Q.QuoteId AND QL.VersionNo=Q.VersionNo
		JOIN    GLClassTypeBusinessType GL ON QL.ClassTypeBusinessTypeId = GL.ClassTypeBusinessTypeId
		GROUP BY	QL.QuoteId,QL.VersionNo
		HAVING count(DISTINCT GL.ClassId)>1
	)
	,QuoteBusinessClassCombinations AS	
	(
		SELECT DISTINCT QL.QuoteId,QL.VersionNo,BC.LookupName BusinessClass, BC.LookupId BusinessClassId
		FROM    dbo.UWQuoteClassBusinessLink QL
		JOIN	QuotesWithMultipleBusinessClasses QM ON QL.QuoteId=QM.QuoteId AND QL.VersionNo=QM.VersionNo
		JOIN GLClassTypeBusinessType GL ON QL.ClassTypeBusinessTypeId = GL.ClassTypeBusinessTypeId
		JOIN dbo.GLLookup BC ON BC.LookupCategoryId = 'BUSINESSCLASS' AND GL.ClassId = BC.LookupId
	)

	-- see http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

	INSERT INTO @multipleBusinessClasses
	(
		QuoteId,
		VersionNo,
		BusinessClass,
		BusinessClassId
	)
	SELECT  DISTINCT
			Main.QuoteId,
			Main.VersionNo,
			replace(left(Main.BusinessClasses, len(Main.BusinessClasses)-1), '&amp;', '&' ) AS BusinessClass,
			left(Main.BusinessClassIds, len(Main.BusinessClassIds)-1) AS BusinessClassId
	FROM
		(
			SELECT DISTINCT		
 					 BC2.QuoteId,
					 BC2.VersionNo,
					 (
						SELECT BC1.BusinessClass+',' AS [text()]
						FROM QuoteBusinessClassCombinations BC1
						WHERE BC1.QuoteId=BC2.QuoteId AND BC1.VersionNo = BC2.VersionNo
						ORDER BY BC1.QuoteId,BC1.VersionNo,BC1.BusinessClass
						FOR XML PATH('')
					 ) BusinessClasses,
					 (
						SELECT BC1.BusinessClassId+'_' AS [text()]
						FROM QuoteBusinessClassCombinations BC1
						WHERE BC1.QuoteId=BC2.QuoteId AND BC1.VersionNo = BC2.VersionNo
						ORDER BY BC1.QuoteId,BC1.VersionNo,BC1.BusinessClass
						FOR XML PATH('')
					 ) BusinessClassIds
			FROM QuoteBusinessClassCombinations BC2
		) Main		
	
	INSERT INTO dbo.GLLookup 
	(
		LookupCategoryId,
		LookupId,
		LookupRef,
		CultureCode,
		LookupName,
		DefaultYN,
		SortOrder,
		SystemYN,
		DeactiveYN,
		CreatedBy,
		CreatedOn
	)
	SELECT DISTINCT
		'BUSINESSCLASS',
		BusinessClassId,
		BusinessClassId,
		'en-GB',
		BusinessClass,
		'N',
		999,
		'Y',
		'Y',
		1,
		getdate()
	FROM @multipleBusinessClasses		
	
---------------------------------------------------------------------------------------------------	
--  Import PolicyVersion
---------------------------------------------------------------------------------------------------	
	EXEC Columbus.WriteLine  'Importing Quote PolicyVersions'
	
	INSERT INTO Columbus.PolicyVersion
	(
		Id
	   ,Policy_Id
	   ,Broker_Id
	   ,Capacity_Id
	   ,Member_Id
	   ,PolicyStatus_Id
	   ,PolicyDateStart
	   ,PolicyDateEnd
	   ,Terms
	   ,Currency_Id
	   ,QuotaShare
	   ,IsTonnageReportable
	   ,IsUnderwriterOnly
	   ,Underwriter_Id
	   ,BusinessClass_Id
	   ,BusinessType_Id
	   ,CreatedBy
	   ,CreatedOn
	   ,UpdatedBy
	   ,UpdatedOn
	   ,VersionNumber
	   ,OrderBoundDate
	   ,DeclarationDescription
	   ,DeclarationBasis_Id
	   ,PremiumBasis_Id
	   ,PolicyDateYear
	   ,WasPreviouslyDeclined
	   ,PreviouslyDeclinedHistory
	   ,IsPremiumAccount
	   ,IsAADApplicable
	   ,CreationOrder
	)
	SELECT
		 Q.QuoteId+@quoteIdOffSet							--Id
		,Q.QuoteId+@quoteIdOffSet							--Policy_Id
		,Q.BrokerId											--Broker_Id
		,CASE WHEN Q.MemberCapacityId = '' THEN NULL
			ELSE Q.MemberCapacityId
		END													--Capacity_Id
		,Q.MemberId											--MemberId
		,Q.StatusId											--PolicyStatus_Id
		,Q.CoverStartDate									--PolicyDateStart
		,Q.CoverEndDate										--PolicyDateEnd
		,Q.EnquiryDescription								--Terms
		,Q.QuoteCurrencyId									--Currency_Id
		,Q.QuotaShareDescription							--QuotaShare
		,Q.Columbus_IsTonnageReportable						--IsTonnageReportable
		,Columbus.YNToBit(Q.UWOnlyIndicatorYN)				--IsUnderwriterOnly
		,UW.UserId											--Underwriter_Id
		,COALESCE(MBC.BusinessClassId, BCT.BusinessClassId)	--BusinessClass_Id
		,COALESCE(MBT.BusinessTypeId, BCT.BusinessTypeId)	--BusinessType_Id
		,Q.CreatedBy										--CreatedBy
		,Q.CreatedOn										--CreatedOn
		,Q.UpdatedBy										--UpdatedBy
		,Q.UpdatedOn										--UpdatedOn
		,0													--VersionNumber
		,Q.Columbus_OrderBoundDate							--OrderBoundDate
		,Q.Columbus_DeclarationDescription					--DeclarationDescription
		,Q.Columbus_DeclarationBasis_Id						--DeclarationBasis_Id
		,Q.Columbus_PremiumBasis_Id							--PremiumBasis_Id
		,Q.PolicyYearId										--PolicyDateYear
		,Columbus.YNToBit(Q.PNIYN)							--WasPreviouslyDeclined
		,Q.PNIDescription									--PreviouslyDeclinedHistory
		,Columbus.YNToBit(Q.PremiumAccountYN)				--IsPremiumAccount
		,Columbus.YNToBit(Q.AADApplicableYN)				--IsAADApplicable
		,1													--CreationOrder
	FROM @importedQuotes I
	JOIN dbo.UWQuote Q ON Q.QuoteId = I.QuoteId AND Q.VersionNo = I.VersionNo
	LEFT JOIN dbo.GLCaseOwners UW ON Q.QuoteCaseIdentity = UW.ObjectId
								 AND UW.BusinessFunction = 'Underwriting'
								 AND UW.BusinessActivity = 'CaptureQuote'
	LEFT JOIN @multipleBusinessClasses MBC ON MBC.QuoteId=Q.QuoteId AND MBC.VersionNo=Q.VersionNo		                         
	LEFT JOIN @multipleBusinessTypes MBT ON MBT.QuoteId=Q.QuoteId AND MBT.VersionNo=Q.VersionNo		                         
	OUTER APPLY (
					SELECT  TOP 1 
							BC.LookupId BusinessClassId,
							BT.LookupId BusinessTypeId
					   FROM dbo.UWQuoteClassBusinessLink QL
					   JOIN dbo.GLClassTypeBusinessType GL ON QL.ClassTypeBusinessTypeId=GL.ClassTypeBusinessTypeId
					   JOIN dbo.GLLookup BC ON GL.ClassId=BC.LookupId AND BC.LookupCategoryId='BUSINESSCLASS'
					   JOIN dbo.GLLookup BT ON GL.BusinessTypeId=BT.LookupId AND BT.LookupCategoryId='BUSINESSTYPE'
					   WHERE QL.QuoteId=Q.QuoteId
						 AND QL.VersionNo=Q.VersionNo
				) BCT
					     
	UPDATE Columbus.Policy
	SET	CurrentPolicyVersion_Id = Id
	WHERE Id BETWEEN @quoteIdOffSet AND @quoteIdOffSet+999999    
	
---------------------------------------------------------------------------------------------------	
--  Import CoverageGroup
---------------------------------------------------------------------------------------------------	
	
	IF(@importCoverageGroups = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote CoverageGroups'
		
		-- Import records missing into QuoteCoverageGroup
		
		DECLARE @maxCoverageGroupId INT
		SELECT @maxCoverageGroupId=max(Id) FROM Columbus.QuoteCoverageGroup
			
		INSERT INTO Columbus.QuoteCoverageGroup
		(
			 Id
			,CoverageGroupNumber
			,PolicyVersion_Id
			,PolicyVersion_Version
			,ReinsuranceExceptionReason
			,ReinsuranceExceptionType_Id
			,CreatedOn
			,CreatedBy
			,UpdatedOn
			,UpdatedBy
		)
		SELECT
			 row_number() OVER (ORDER BY QuoteId)+@maxCoverageGroupId	--Id
			,RO.CoverageGroupNo											--CoverageGroupNumber
			,RO.QuoteId													--PolicyVersion_Id
			,RO.VersionNo												--PolicyVersion_Version
			,null														--ReinsuranceExceptionReason
			,1															--ReinsuranceExceptionType_Id
			,RO.CreatedOn												--CreatedOn
			,RO.CreatedBy												--CreatedBy
			,RO.UpdatedOn												--UpdatedOn
			,RO.UpdatedBy												--UpdatedBy
		FROM    
			UWQuoteRO RO
		WHERE	
			NOT EXISTS (SELECT 1 
			              FROM Columbus.QuoteCoverageGroup CG 
			             WHERE CG.PolicyVersion_Id=RO.QuoteId 
			               AND CG.PolicyVersion_Version=RO.VersionNo
			               AND CG.CoverageGroupNumber=RO.CoverageGroupNo)	
		GROUP BY
			RO.CoverageGroupNo
			,RO.QuoteId		
			,RO.VersionNo	
			,RO.CreatedOn	
			,RO.CreatedBy	
			,RO.UpdatedOn	
			,RO.UpdatedBy	
	
		
		INSERT INTO Columbus.CoverageGroup
		(
			 Id
			,CoverageGroupNumber
			,PolicyVersion_Id
			,ReinsuranceExceptionReason
			,ReinsuranceExceptionType_Id
			,CreatedOn
			,CreatedBy
			,UpdatedOn
			,UpdatedBy
		)
		SELECT
			 QCG.Id+@quoteIdOffSet					--Id
			,QCG.CoverageGroupNumber				--CoverageGroupNumber
			,QCG.PolicyVersion_Id+@quoteIdOffSet	--PolicyVersion_Id
			,QCG.ReinsuranceExceptionReason			--ReinsuranceExceptionReason
			,QCG.ReinsuranceExceptionType_Id		--ReinsuranceExceptionType_Id
			,QCG.CreatedOn							--CreatedOn
			,QCG.CreatedBy							--CreatedBy
			,QCG.UpdatedOn							--UpdatedOn
			,QCG.UpdatedBy							--UpdatedBy
		FROM	Columbus.QuoteCoverageGroup QCG	
		JOIN	@importedQuotes Q ON QCG.PolicyVersion_Id=Q.QuoteId AND QCG.PolicyVersion_Version=Q.VersionNo
	END
	
---------------------------------------------------------------------------------------------------	
--  Import Coverages
---------------------------------------------------------------------------------------------------	
	
	DECLARE @importedCoverages TABLE
	(
		CoverageId INT PRIMARY KEY
	)
	
	IF(@importCoverages = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote Coverages'
		
		DECLARE @distinctCoverages TABLE
		(
			QuoteId INT,
			VersionNo INT,
			CoverageGroupNo INT,
			CoverageId INT
		)
		
		DECLARE @mostRecentCoverages TABLE
		(
			QuoteROCoverageLinkId INT PRIMARY KEY,
			CoverageGroupId INT
		)
			
		INSERT INTO @distinctCoverages
		(
			QuoteId, 
			VersionNo,
			CoverageGroupNo, 
			CoverageId
		)
		SELECT DISTINCT QuoteId, 
			VersionNo,
			CoverageGroupNo, 
			CoverageId
		FROM dbo.UWQuoteROCoverageLink
		
		INSERT INTO @mostRecentCoverages
		(
			QuoteROCoverageLinkId,
			CoverageGroupId
		)
		SELECT MostRecentCoverages.QuoteROCoverageLinkId
			, QCG.Id
		FROM @importedQuotes Q
			JOIN Columbus.QuoteCoverageGroup QCG ON QCG.PolicyVersion_Id = Q.QuoteId
				AND QCG.PolicyVersion_Version = Q.VersionNo
			JOIN @distinctCoverages DC ON DC.QuoteId = Q.QuoteId  
				AND DC.VersionNo = Q.VersionNo
				AND DC.CoverageGroupNo = QCG.CoverageGroupNumber
			OUTER APPLY 
			(
				SELECT TOP 1 LINK.QuoteROCoverageLinkId
				FROM UWQuoteROCoverageLink LINK
				WHERE LINK.QuoteId = DC.QuoteId AND LINK.VersionNo = DC.VersionNo
					AND LINK.CoverageGroupNo = DC.CoverageGroupNo
					AND LINK.CoverageId = DC.CoverageId
				ORDER BY COALESCE(LINK.CreatedOn, LINK.UpdatedOn) DESC, LINK.QuoteROCoverageLinkId DESC
			) AS MostRecentCoverages
		
		INSERT INTO Columbus.Coverage
		(
			 Id
			,CoverageGroup_Id
			,GeneralCoverage_Id
			,IsIncluded
			,IsPoolable
			,ReinsuranceIndicatorId
			,IsLimitOfLiabilityAsPerRules
			,LimitOfLiabilityCurrencyId
			,LimitOfLiabilityAmount
			,IsDeductibleAsPerRules
			,DeductibleCurrencyId
			,DeductibleAmount
			,NarrativeTextId
			,IsIncludedInETP
			,PremiumById
			,Excess
			,ExcessCurrencyId
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,UWCoverCoverageId
			,CoverCoverage_Id
		)
		OUTPUT INSERTED.Id-@quoteIdOffSet INTO @importedCoverages
		SELECT 
			 COV.QuoteROCoverageLinkId+@quoteIdOffSet				--Id
			,[@mostRecentCoverages].CoverageGroupId+@quoteIdOffSet	--CoverageGroup_Id
			,COV.CoverageId											--CoverageId
			,Columbus.YNToBit(COV.IncludeExcludeInd)				--IsIncluded
			,Columbus.YNToBit(COV.PoolableYN)						--IsPoolable
			,COV.ReinsuranceInd										--ReinsuranceIndicatorId
			,Columbus.YNToBit(COV.AsPerRulesLimitOfLiabilityYN)		--IsLimitOfLiabilityAsPerRules
			,COV.LOLTrnCurrencyId									--LimitOfLiabilityCurrencyId
			,COV.LOLTrnAmt											--LimitOfLiabilityAmount
			,Columbus.YNToBit(COV.AsPerRulesDeductibleAmountYN)		--IsDeductibleAsPerRules
			,COV.DeductibleTrnCurrencyId							--DeductibleCurrencyId
			,COV.DeductibleTrnAmt									--DeductibleAmount
			,COV.NarrativeTextId									--NarrativeTextId
			,Columbus.YNToBit(COV.IncludedInETPYN)					--IsIncludedInETP
			,COV.PremiumBy											--PremiumById
			,COV.Columbus_Excess		        					--Excess
			,COV.Columbus_ExcessCurrencyId							--ExcessCurrencyId
			,COV.CreatedBy											--CreatedBy
			,COV.CreatedOn											--CreatedOn
			,COV.UpdatedBy											--UpdatedBy
			,COV.UpdatedOn											--UpdatedOn
			,NULL													--UWCoverCoverageId
			,NULL													--CoverCoverage_Id
		FROM @mostRecentCoverages 
			JOIN UWQuoteROCoverageLink COV ON COV.QuoteROCoverageLinkId = [@mostRecentCoverages].QuoteROCoverageLinkId
	END
	
---------------------------------------------------------------------------------------------------	
--  Import Rules
---------------------------------------------------------------------------------------------------	
	DECLARE @importedRules TABLE
	(
		RuleId INT PRIMARY KEY
	)
	
	IF(@importRules = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote Rules'
	
		INSERT INTO Columbus.[Rule]
		(
			 Id
			,GeneralRule_Id
			,Coverage_Id
			,IsIncluded
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,UWCoverRuleId
			,CoverRule_Id
		)
		OUTPUT INSERTED.Id-@quoteIdOffset INTO @importedRules
		SELECT 
			 RUL.QuoteROCoverageRuleLinkId+@quoteIdOffSet			--Id
			,RUL.RuleId												--RuleId
			,RUL.QuoteROCoverageLinkId+@quoteIdOffSet				--Coverage_Id
			,Columbus.YNToBit(RUL.IncludeYN)						--IsIncluded
			,RUL.CreatedBy											--CreatedBy
			,RUL.CreatedOn											--CreatedOn
			,RUL.UpdatedBy											--UpdatedBy
			,RUL.UpdatedOn											--UpdatedOn
			,null													--UWCoverRuleId
			,null													--CoverRule_Id
		FROM dbo.UWQuoteROCoverageRuleLink RUL
			JOIN @importedCoverages COV ON RUL.QuoteROCoverageLinkId = COV.CoverageId
	END
	
---------------------------------------------------------------------------------------------------	
--  Import Clauses
---------------------------------------------------------------------------------------------------	
	
	IF(@importClauses = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote Clauses'
	
		INSERT INTO Columbus.Clause
		(
			 Id
			,GeneralClause_Id
			,Rule_Id
			,IsPrinted
			,ClauseIncludeIndicatorId
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,UWCoverClauseId
			,CoverClause_Id
		)
		SELECT
			CL.QuoteROCoverageRuleClauseLinkId+@quoteIdOffSet	--Id
		   ,CL.ClauseId											--ClauseId
		   ,CL.QuoteROCoverageRuleLinkId+@quoteIdOffSet			--Rule_Id
		   ,Columbus.YNToBit(CL.PrintYN)						--IsPrinted
		   ,CL.IncludeInd										--ClauseIncludeIndicatorId
		   ,CL.CreatedBy										--CreatedBy
		   ,CL.CreatedOn										--CreatedOn
		   ,CL.UpdatedBy										--UpdatedBy
		   ,CL.UpdatedOn										--UpdatedOn
		   ,null												--UWCoverClauseId
		   ,null												--CoverClause_Id
		FROM dbo.UWQuoteROCoverageRuleClauseLink CL
			JOIN @importedRules RUL ON CL.QuoteROCoverageRuleLinkId=RUL.RuleId
	END    
	   
---------------------------------------------------------------------------------------------------	
--  Import PolicyItem
---------------------------------------------------------------------------------------------------	
	
	DECLARE @importedRiskObjects TABLE
	(
		QuoteROId INT PRIMARY KEY
	)
	
	IF(@importPolicyItems = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote PolicyItems'
	
		;WITH InsuredParties AS
		(
		SELECT 
			CertificateMemberId,
			AccountingMemberId,
			RODetailsId,
			BusinessTypeId,
			QuoteId,
			VersionNo
		FROM UWQuoteROCertActgMember
		GROUP BY 	
			CertificateMemberId,
			AccountingMemberId,
			RODetailsId,
			BusinessTypeId,
			QuoteId,
			VersionNo,
			AmdId,
			AmendmentRequestId,
			CreatedBy,
			CreatedOn,
			UpdatedBy,
			UpdatedOn
		),
		RiskDates AS
		(
		    SELECT DISTINCT
			    QuoteROId,
			    OnRiskDate,
			    OffRiskDate
		    FROM dbo.UWQuoteRORiskDates	
		    WHERE NOT(OnRiskDate IS NULL AND OffRiskDate IS NULL)
		),
		DuplicateDeclarationItems AS
		(
            select 
                max(ro.QuoteROId) as QuoteROId
            from dbo.UWQuoteRo ro
            join (
                select 
                    ro.QuoteId, ro.VersionNo 
                from dbo.UWQuoteRO ro
                JOIN @importedQuotes Q ON RO.QuoteId=Q.QuoteId AND RO.VersionNo=Q.VersionNo
                where Columbus_PolicyItemType_Id = 2
                group by ro.QuoteId, ro.VersionNo
                having count(*) > 1
            ) as QuoteWithMultipleDeclarationItems on QuoteWithMultipleDeclarationItems.QuoteId = ro.QuoteId
                                                      and QuoteWithMultipleDeclarationItems.VersionNo = ro.VersionNo
            where ro.Columbus_PolicyItemType_Id = 2
            group by ro.QuoteId, ro.VersionNo
		)
		INSERT INTO Columbus.PolicyItem
		(
			 Id
			,PolicyVersion_Id
			,CoverageGroup_Id
			,OnRiskDate
			,OffRiskDate
			,Ship_Id
			,EnteredTonnage
			,IsCofrRequested
			,WasPreviouslySurveyed
			,PreviousSurveyDetails
			,IsHighRisk
			,IsLaidUp
			,TradingArea_Id
			,HaveSentSurvey
			,IsSurveyRequired
			,HullValue
			,HullValueCurrencyId
			,CoverageTemplateId
			,Certificate_Id
			,CertificateMember_Id
			,AccountingMember_Id
			,OrderPercentage
			,IsNewAttachment
			,HeldCovered
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,UWCoverROPolicyItemId
			,PolicyItemTypeId
			,CoverPolicyItem_Id
		 )
		 OUTPUT INSERTED.Id-@quoteIdOffSet INTO @importedRiskObjects
		 SELECT
			RO.QuoteROId+@quoteIdOffSet								--Id
		   ,RO.QuoteId+@quoteIdOffSet								--PolicyVersion_Id
		   ,QCG.Id+@quoteIdOffSet									--CoverageGroup_Id
		   ,DAT.OnRiskDate											--OnRiskDate
		   ,DAT.OffRiskDate											--OffRiskDate
		   ,ROSH.ShipId												--Ship_Id
		   ,COALESCE(ROSH.EnteredTonnage,0)							--EnteredTonnage
		   ,COALESCE(Columbus.YNToBit(ROSH.COFRRequestedYN),0)		--IsCofrRequested
		   ,COALESCE(Columbus.YNToBit(ROSH.PNIBeforeYN),0)			--WasPreviouslySurveyed
		   ,ROSH.PNIBeforeDetails									--PreviousSurveyDetails
		   ,COALESCE(Columbus.YNToBit(ROSH.HighRiskYN),0)			--IsHighRisk
		   ,COALESCE(Columbus.YNToBit(ROSH.LaidUpIndicatorYN),0)	--IsLaidUp
		   ,ROSH.TradingAreaId										--TradingArea_Id
		   ,COALESCE(Columbus.YNToBit(ROSH.SurveySentYN),0)			--HaveSentSurvey
		   ,COALESCE(Columbus.YNToBit(ROSH.SurveyRequiredYN),0)		--IsSurveyRequired
		   ,ROSH.HullValue											--HullValue
		   ,ROSH.HullValueCurrencyId								--HullValueCurrencyId
		   ,RO.CoverageTemplateId									--CoverageTemplateId
		   ,null													--Certificate_Id: will be removed
		   ,IP.CertificateMemberId									--CertificateMember_Id
		   ,IP.AccountingMemberId									--AccountingMember_Id
		   ,RO.Columbus_OrderPercentage								--OrderPercentage
		   ,RO.Columbus_IsNewAttachment								--IsNewAttachment
		   ,null													--HeldCovered: will be removed
		   ,RO.CreatedBy											--CreatedBy
		   ,RO.CreatedOn											--CreatedOn
		   ,RO.UpdatedBy											--UpdatedBy
		   ,RO.UpdatedOn											--UpdatedOn
		   ,null													--UWCoverROPolicyItemId: will be removed
		   ,RO.Columbus_PolicyItemType_Id							--PolicyItemTypeId
		   ,null													--CoverPolicyItem_Id
		FROM dbo.UWQuoteRO RO
		JOIN @importedQuotes Q ON RO.QuoteId=Q.QuoteId AND RO.VersionNo=Q.VersionNo
		LEFT JOIN dbo.UWQuoteROShip ROSH ON RO.RODetailsId = ROSH.QuoteROShipId
										AND RO.Columbus_PolicyItemType_Id=1
		LEFT JOIN RiskDates DAT ON RO.QuoteROId=DAT.QuoteROId
		LEFT JOIN InsuredParties IP ON RO.RODetailsId = IP.RODetailsId 
								   AND RO.BusinessTypeId = IP.BusinessTypeId
								   AND RO.QuoteId = IP.QuoteId
								   AND RO.VersionNo = IP.VersionNo	
		JOIN Columbus.QuoteCoverageGroup QCG ON RO.QuoteId = QCG.PolicyVersion_Id
											AND RO.VersionNo = QCG.PolicyVersion_Version
											AND RO.CoverageGroupNo = QCG.CoverageGroupNumber						       
		WHERE 
		(
			RO.Columbus_PolicyItemType_Id=2 
			OR 	ROSH.QuoteROShipId IS NOT NULL
		)
		and ro.QuoteROId not in (
		    select 
		        QuoteROId
		    from DuplicateDeclarationItems
		)
	END
	
---------------------------------------------------------------------------------------------------	
--  Import Joint Entrant
---------------------------------------------------------------------------------------------------	
	IF(@importJointEntrants = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote JointEntrants'
	
		INSERT INTO Columbus.JointEntrant
		(
			Id
		   ,PolicyItem_Id
		   ,Organisation_Id
		   ,Capacity_Id
		   ,IsPrincipalAssured
		   ,HaveReceivedConfirmation
		   ,IsDocumentAttached
		   ,CreatedBy
		   ,CreatedOn
		   ,UpdatedBy
		   ,UpdatedOn
		   ,SequenceNumber
		   ,UWCoverJointEntrantId
		   ,CoverJointEntrant_Id
		)
		SELECT
		   JE.QuoteROJEId+@quoteIdOffSet				--Id
		   ,RO.QuoteROId+@quoteIdOffSet					--PolicyItem_Id
		   ,JE.JointEntrantId							--Organisation_Id
		   ,JE.CapacityTypeId							--Capacity_Id
		   ,Columbus.YNToBit(JE.PrincipalAssuredYN)		--IsPrincipalAssured
		   ,Columbus.YNToBit(JE.ConfirmationYN)			--HaveReceivedConfirmation
		   ,Columbus.YNToBit(DocumentAttachedYN)		--IsDocumentAttached
		   ,JE.CreatedBy								--CreatedBy
		   ,JE.CreatedOn								--CreatedOn
		   ,JE.UpdatedBy								--UpdatedBy
		   ,JE.UpdatedOn								--UpdatedOn
		   ,-1											--SequenceNumber
		   ,null										--UWCoverJointEntrantId
		   ,null										--CoverJointEntrant_Id
		FROM dbo.UWQuoteROJE JE 
			JOIN dbo.UWQuoteRO RO ON JE.RODetailsId = RO.RODetailsId AND JE.QuoteId=RO.QuoteId AND JE.VersionNo=RO.VersionNo  
			JOIN @importedRiskObjects IRO ON IRO.QuoteROId = RO.QuoteROId
			JOIN @importedQuotes Q ON RO.QuoteId=Q.QuoteId AND RO.VersionNo=Q.VersionNo
	END
---------------------------------------------------------------------------------------------------	
--  Import Coassured
---------------------------------------------------------------------------------------------------	
	IF(@importCoAssureds = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote CoAssured'
	
		INSERT INTO [Columbus].[CoAssured]
			   ([Id]
			   ,[PolicyItem_Id]
			   ,[Name]
			   ,[Capacity_Id]
			   ,[CreatedBy]
			   ,[CreatedOn]
			   ,[UpdatedBy]
			   ,[UpdatedOn]
			   ,[SequenceNumber]
			   ,[UWCoverCoAssuredId]
			   ,[CoverCoAssured_Id])
		 select
				COA.QuoteROCoAssuredId+@quoteIdOffSet
			   ,RO.QuoteROId+@quoteIdOffSet			--PolicyItem_Id
			   ,COA.CoAssuredName					--Name
			   ,COA.CapacityId						--Capacity_Id
			   ,COA.CreatedBy						--CreatedBy
			   ,COA.CreatedOn						--CreatedOn
			   ,COA.UpdatedBy						--UpdatedBy
			   ,COA.UpdatedOn						--UpdatedOn
			   ,COA.SequenceNo						--SequenceNumber
			   ,NULL								--UWCoverCoAssuredId
			   ,NULL								--CoverCoAssured_Id
		from
			dbo.UWQuoteROCoAssured COA
				JOIN dbo.UWQuoteRO RO ON RO.QuoteId = COA.QuoteId
					AND RO.VersionNo = COA.VersionNo
					AND RO.RODetailsId = COA.RODetailsId
			JOIN @importedRiskObjects IRO ON IRO.QuoteROId = RO.QuoteROId
			JOIN @importedQuotes Q ON RO.QuoteId=Q.QuoteId AND RO.VersionNo=Q.VersionNo
	END
			
---------------------------------------------------------------------------------------------------	
--  Import Mortgagees
---------------------------------------------------------------------------------------------------	
	 
	 IF(@importMortgagees = 1)
	 BEGIN
		EXEC Columbus.WriteLine  'Importing Quote Mortgagees'
	 
		 INSERT INTO [Columbus].[Mortgagee]
			   ([Id]
			   ,[PolicyItem_Id]
			   ,[Organisation_Id]
			   ,[IsFirstMortgagee]
			   ,[IsConsortiumLeader]
			   ,[EffectiveDate]
			   ,[ExpiryDate]
			   ,[CreatedBy]
			   ,[CreatedOn]
			   ,[UpdatedBy]
			   ,[UpdatedOn]
			   ,[SequenceNumber]
			   ,[UWCoverMortgageeId]
			   ,[CoverMortgagee_Id])
		 SELECT
			   QuoteROMortgageesId+@quoteIdOffSet
			   ,RO.QuoteROId+@quoteIdOffSet						--PolicyItem_Id
			   ,M.MortgageeId									--Organisation_Id
			   ,Columbus.YNToBit(M.FirstMortgageeYN)			--IsFirstMortgagee
			   ,Columbus.YNToBit(M.IsConsortiumYN)				--IsConsortiumLeader
			   ,M.EffectiveDate									--EffectiveDate
			   ,M.ExpiryDate									--ExpiryDate
			   ,M.CreatedBy										--CreatedBy
			   ,M.CreatedOn										--CreatedOn
			   ,M.UpdatedBy										--UpdatedBy
			   ,M.UpdatedOn										--UpdatedOn
			   ,M.SequenceNo									--SequenceNumber
			   ,NULL											--UWCoverMortgageeId
			   ,NULL											--CoverMortgagee_Id
		 FROM
				dbo.UWQuoteROMortgagees M
					JOIN dbo.UWQuoteRO RO ON RO.QuoteId = M.QuoteId
						AND RO.VersionNo = M.VersionNo
						AND RO.RODetailsId = M.RODetailsId
					JOIN @importedRiskObjects IRO ON IRO.QuoteROId = RO.QuoteROId
					JOIN @importedQuotes Q ON RO.QuoteId=Q.QuoteId AND RO.VersionNo=Q.VersionNo
	END
	
---------------------------------------------------------------------------------------------------	
--  Import GXL categories
---------------------------------------------------------------------------------------------------	
	
	IF(@importGxlCategories = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote GXLs'
	
		INSERT INTO Columbus.PolicyItemGxlCategory
			   (Id
			   ,PolicyItem_Id
			   ,GxlCategory_Id
			   ,EffectiveDate
			   ,OldCoverPolicyItemGxlCategoryId
			   ,CreatedBy
			   ,CreatedOn
			   ,UpdatedBy
			   ,UpdatedOn
			   ,CoverPolicyItemGxlCategory_Id)
		SELECT
			   G.Id+@quoteIdOffSet
			   ,G.PolicyItem_Id+@quoteIdOffSet
			   ,G.GxlCategory
			   ,G.EffectiveDate
			   ,G.CoverPolicyItemGxlCategoryId
			   ,G.CreatedBy
			   ,G.CreatedOn
			   ,G.UpdatedBy
			   ,G.UpdatedOn
			   ,NULL
		FROM
			Columbus.QuotePolicyItemGxlCategory G
				JOIN dbo.UWQuoteRO RO ON G.PolicyItem_Id = RO.QuoteROId
				JOIN @importedRiskObjects IRO ON IRO.QuoteROId = RO.QuoteROId
				JOIN @importedQuotes Q ON RO.QuoteId=Q.QuoteId AND RO.VersionNo=Q.VersionNo
				LEFT JOIN dbo.UWQuoteROShip ROSH ON RO.RODetailsId = ROSH.QuoteROShipId
									AND RO.Columbus_PolicyItemType_Id=1
		WHERE RO.Columbus_PolicyItemType_Id=2
		   OR ROSH.QuoteROShipId IS NOT NULL
	END
	
	
---------------------------------------------------------------------------------------------------	
--  Import Crew
---------------------------------------------------------------------------------------------------	
	
	IF(@importCrews = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote Crew'
		
		DECLARE @importedCrew TABLE
		(
			QuoteROShipCrewId		INT
		   ,Columbus_Migrated_Id	INT
		   ,PolicyItem_Id			INT
		   ,BusinessClassId			NVARCHAR(6)
		   ,Number					INT
		   ,Country_Id				NVARCHAR(3)
		   ,EffectiveDate			DATETIME
		   ,ExpiryDate				DATETIME
		   ,CreatedBy				INT
		   ,CreatedOn				DATETIME
		   ,UpdatedBy				INT
		   ,UpdatedOn				DATETIME
		)
		
		INSERT INTO @importedCrew
		SELECT
			C.QuoteROShipCrewId
		   ,@quoteIdOffSet
				+(CASE WHEN RO.BusinessClassId='P' THEN @pandiBusinessClassOffset 
					ELSE @defenceBusinessClassIdOffset 
				END)
				+(@crewFactor*(row_number() OVER(PARTITION BY C.QuoteROShipCrewId,RO.Columbus_PolicyItemType_Id,RO.BusinessClassId ORDER BY C.QuoteROShipCrewId))+C.QuoteROShipCrewId)
		   ,RO.QuoteROId+@quoteIdOffSet		--PolicyItem_Id
		   ,RO.BusinessClassId				--BusinessClassId
		   ,C.CrewNos						--Number
		   ,C.CrewNationalityId				--Country_Id
		   ,C.EffectiveDate					--EffectiveDate
		   ,C.ExpiryDate					--ExpiryDate
		   ,C.CreatedBy						--CreatedBy
		   ,C.CreatedOn						--CreatedOn
		   ,C.UpdatedBy						--UpdatedBy
		   ,C.UpdatedOn						--UpdatedOn
		FROM
			dbo.UWQuoteRO RO
			JOIN @importedRiskObjects IRO ON IRO.QuoteROId = RO.QuoteROId
			JOIN @importedQuotes Q ON RO.QuoteId=Q.QuoteId AND RO.VersionNo=Q.VersionNo
			JOIN dbo.UWQuoteROShip ROSH ON RO.RODetailsId = ROSH.QuoteROShipId
			JOIN UWQuoteROShipCrewDetails C ON C.QuoteROShipId = ROSH.QuoteROShipId
		WHERE
			RO.Columbus_PolicyItemType_Id = 1
	
		UPDATE Crew
		SET Crew.Columbus_Migrated_Id = iCrew.Columbus_Migrated_Id
		FROM UWQuoteROShipCrewDetails Crew
		JOIN @importedCrew iCrew ON Crew.QuoteROShipCrewId=iCrew.QuoteROShipCrewId

		INSERT INTO Columbus.PolicyItemCompany
		   (Id
		   ,PolicyItem_Id
		   ,Type
		   ,Number
		   ,Country_Id
		   ,EffectiveDate
		   ,ExpiryDate
		   ,CreatedBy
		   ,CreatedOn
		   ,UpdatedBy
		   ,UpdatedOn
		   ,UWCoverCompanyId
		   ,CoverPolicyItemCompany_Id)
		SELECT
			Columbus_Migrated_Id		--Id
		   ,PolicyItem_Id				--PolicyItem_Id
		   ,'C'							--Type
		   ,Number						--Number
		   ,Country_Id					--Country_Id
		   ,EffectiveDate				--EffectiveDate
		   ,ExpiryDate					--ExpiryDate
		   ,CreatedBy					--CreatedBy
		   ,CreatedOn					--CreatedOn
		   ,UpdatedBy					--UpdatedBy
		   ,UpdatedOn					--UpdatedOn
		   ,NULL						--UWCoverCompanyId
		   ,NULL						--CoverPolicyItemCompany_Id
		FROM @importedCrew
		ORDER BY Columbus_Migrated_Id		   

	END
	
---------------------------------------------------------------------------------------------------	
--  Import Officers
---------------------------------------------------------------------------------------------------	
		
	IF(@importOfficers = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote Officers'
		
		DECLARE @importedOfficers TABLE
		(
			QuoteROShipOfficerId	INT
		   ,Columbus_Migrated_Id	INT
		   ,PolicyItem_Id			INT
		   ,Number					INT
		   ,Country_Id				NVARCHAR(3)
		   ,EffectiveDate			DATETIME
		   ,ExpiryDate				DATETIME
		   ,CreatedBy				INT
		   ,CreatedOn				DATETIME
		   ,UpdatedBy				INT
		   ,UpdatedOn				DATETIME
		)
		
		INSERT INTO @importedOfficers
		SELECT
			O.QuoteROShipOfficerId
		   ,@quoteIdOffSet
				+@officerIdOffset
				+(CASE WHEN RO.BusinessClassId='P' THEN @pandiBusinessClassOffset 
					ELSE @defenceBusinessClassIdOffset 
				END)
				+(@crewFactor*(row_number() OVER(PARTITION BY O.QuoteROShipOfficerId,RO.Columbus_PolicyItemType_Id,RO.BusinessClassId ORDER BY O.QuoteROShipOfficerId))+O.QuoteROShipOfficerId)
           ,RO.QuoteROId+@quoteIdOffSet			--PolicyItem_Id
           ,O.OfficerNos						--Number
           ,O.OfficerNationalityId				--Country_Id
           ,O.EffectiveDate						--EffectiveDate
           ,O.ExpiryDate						--ExpiryDate
           ,O.CreatedBy							--CreatedBy
           ,O.CreatedOn							--CreatedOn
           ,O.UpdatedBy							--UpdatedBy
           ,O.UpdatedOn							--UpdatedOn
		FROM
			dbo.UWQuoteRO RO
			JOIN @importedRiskObjects IRO ON IRO.QuoteROId = RO.QuoteROId
			JOIN @importedQuotes Q ON RO.QuoteId=Q.QuoteId AND RO.VersionNo=Q.VersionNo
			JOIN dbo.UWQuoteROShip ROSH ON RO.RODetailsId = ROSH.QuoteROShipId
			JOIN dbo.UWQuoteROShipOfficerDetails O ON O.QuoteROShipId = ROSH.QuoteROShipId
		WHERE
			RO.Columbus_PolicyItemType_Id = 1

		UPDATE Officers
		SET Officers.Columbus_Migrated_Id = iOff.Columbus_Migrated_Id
		FROM UWQuoteROShipOfficerDetails Officers
		JOIN @importedOfficers iOff ON Officers.QuoteROShipOfficerId=iOff.QuoteROShipOfficerId		

		INSERT INTO Columbus.PolicyItemCompany
           (Id
           ,PolicyItem_Id
           ,Type
           ,Number
           ,Country_Id
           ,EffectiveDate
           ,ExpiryDate
           ,CreatedBy
           ,CreatedOn
           ,UpdatedBy
           ,UpdatedOn
           ,UWCoverCompanyId
           ,CoverPolicyItemCompany_Id)
        SELECT
			Columbus_Migrated_Id		--Id
		   ,PolicyItem_Id				--PolicyItem_Id
		   ,'O'							--Type
		   ,Number						--Number
		   ,Country_Id					--Country_Id
		   ,EffectiveDate				--EffectiveDate
		   ,ExpiryDate					--ExpiryDate
		   ,CreatedBy					--CreatedBy
		   ,CreatedOn					--CreatedOn
		   ,UpdatedBy					--UpdatedBy
		   ,UpdatedOn					--UpdatedOn
		   ,NULL						--UWCoverCompanyId
		   ,NULL						--CoverPolicyItemCompany_Id
		FROM @importedOfficers
		ORDER BY Columbus_Migrated_Id			

	END
	
---------------------------------------------------------------------------------------------------	
--  Import PolicyVersionAction
---------------------------------------------------------------------------------------------------	
		
	IF(@importPolicyVersionActions = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote PolicyVersionAction'
	
		INSERT INTO Columbus.PolicyVersionAction
           (Id
           ,Reason
           ,Type
           ,ReopenDate
           ,Date
           ,CreatedBy
           ,CreatedOn
           ,UpdatedBy
           ,UpdatedOn
           ,PolicyVersion_Id)
		SELECT
           A.QuoteActionReasonId+@quoteIdOffSet		--Id
           ,A.Reason								--Reason
           ,A.ActionId								--Type
           ,A.ReopenDate							--ReopenDate
           ,A.ActionDate							--Date
           ,A.CreatedBy								--CreatedBy
           ,A.CreatedOn								--CreatedOn           
           ,A.UpdatedBy								--UpdatedBy
           ,A.UpdatedOn								--UpdatedOn
           ,A.QuoteId+@quoteIdOffSet				--PolicyVersion_Id
        FROM
			@importedQuotes Q 
				JOIN UWQuoteActionReason A ON A.QuoteId = Q.QuoteId AND A.VersionNo = Q.VersionNo
	END
	

---------------------------------------------------------------------------------------------------	
--  Import Premiums
---------------------------------------------------------------------------------------------------	

	DECLARE @insertedPremiums TABLE (Id INT PRIMARY KEY CLUSTERED)
	
	IF(@importPremiums = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote Premiums'
	
		;WITH DistinctPremiums AS
		(
			SELECT P.PremiumTypeId,P.QuoteId,P.VersionNo,P.QuoteROId,P.CoverageId
			FROM UWQuoteROPremium P
			JOIN @importedQuotes Q ON P.QuoteId=Q.QuoteId AND P.VersionNo=Q.VersionNo
			WHERE P.PremiumTypeId IN ('ETP','APC','APNC')
			GROUP BY P.PremiumTypeId,P.QuoteId,P.VersionNo,P.QuoteROId,P.CoverageId
		)
		, LatestPremium AS
		(
			SELECT LatestPremium.QuoteROPremiumId
			FROM DistinctPremiums
			OUTER APPLY 
			(	
				SELECT TOP 1 P.QuoteROPremiumId
				FROM UWQuoteROPremium P 
				WHERE P.PremiumTypeId=DistinctPremiums.PremiumTypeId
				  AND P.QuoteId=DistinctPremiums.QuoteId
				  AND P.VersionNo=DistinctPremiums.VersionNo
				  AND (P.QuoteROId=DistinctPremiums.QuoteROId OR (P.QuoteROId IS NULL AND DistinctPremiums.QuoteROId IS NULL))
				  AND (P.CoverageId=DistinctPremiums.CoverageId OR (P.CoverageId IS NULL AND DistinctPremiums.CoverageId IS NULL))
				ORDER BY coalesce(P.UpdatedOn, P.CreatedOn) DESC, P.QuoteROPremiumId DESC
			) AS LatestPremium
		)
		INSERT INTO Columbus.Premium
		(
			 Id
			,PolicyVersion_Id
			,PremiumMode_Id
			,Mutuality_Id
			,Rate
			,InstalmentPlan_Id
			,PremiumType_Id
			,TotalTransactionCurrency_Id
			,TotalTransactionTotalValue
			,TotalClubCurrency_Id
			,TotalClubTotalValue
			,TotalMemberCurrency_Id
			,TotalMemberTotalValue
			,BrokerageTotalTransactionCurrency_Id
			,BrokerageTotalTransactionTotalValue
			,BrokerageTotalClubCurrency_Id
			,BrokerageTotalClubTotalValue
			,BrokerageTotalMemberCurrency_Id
			,BrokerageTotalMemberTotalValue
			,BrokeragePercentage
			,TaxRate_Id
			,PolicyItem_Id
			,LaidupRate
			,CallPercentage
			,MinimumDays
			,ExtensionDays
			,IsChecked
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,GeneralCoverage_Id
			,AdditionalPremiumReason_Id
			,Remarks
			,UWCoverPremiumId
			,BackToBack
			,IsCharged
			,CoverPremium_Id
			,Discriminator
		)
		OUTPUT INSERTED.Id-@quoteIdOffSet INTO @insertedPremiums
		-- premiums by ship
		SELECT
			 PR.QuoteROPremiumId+@quoteIdOffSet			--Id
			,PR.QuoteId+@quoteIdOffSet					--PolicyVersion_Id
			,PR.PremiumModeId							--PremiumMode_Id
			,PR.MutualityTypeId							--Mutuality_Id
			,PR.PremiumRate								--Rate
			,PR.InstallmentPlanId						--InstalmentPlan_Id
			,PR.PremiumTypeId							--PremiumType_Id
			,PR.PremiumTrnCurrencyId					--TotalTransactionCurrency_Id
			,PR.PremiumTrnAmt							--TotalTransactionTotalValue
			,PR.PremiumClubCurrencyId					--TotalClubCurrency_Id
			,PR.PremiumClubAmt							--TotalClubTotalValue
			,PR.PremiumMembCurrencyId					--TotalMemberCurrency_Id
			,PR.PremiumMembAmt							--TotalMemberTotalValue
			,PR.BrokerageTrnCurrencyId					--BrokerageTotalTransactionCurrency_Id
			,PR.BrokerageTrnAmt							--BrokerageTotalTransactionTotalValue
			,PR.BrokerageClubCurrencyId					--BrokerageTotalClubCurrency_Id
			,PR.BrokerageClubAmt						--BrokerageTotalClubTotalValue
			,PR.BrokerageMembCurrencyId					--BrokerageTotalMemberCurrency_Id
			,PR.BrokerageMembAmt						--BrokerageTotalMemberTotalValue
			,PR.BrokeragePercentage						--BrokeragePercentage
			,PR.TaxCodeId								--TaxRate_Id
			,PR.QuoteROId+@quoteIdOffSet				--PolicyItem_Id
			,PR.LaidupRate								--LaidupRate
			,PR.CallPercentage							--CallPercentage
			,PR.MinimumDays								--MinimumDays
			,PR.ExtensionDays							--ExtensionDays
			,PR.Columbus_IsChecked						--IsChecked
			,PR.CreatedBy								--CreatedBy
			,PR.CreatedOn								--CreatedOn
			,PR.UpdatedBy								--UpdatedBy
			,PR.UpdatedOn								--UpdatedOn
			,PR.CoverageId								--GeneralCoverage_Id
			,PR.ReasonCode								--AdditionalPremiumReason_Id
			,PR.Remarks									--Remarks
			,NULL										--UWCoverPremiumId
			,PR.Columbus_BackToBack						--BackToBack
			,PR.Columbus_IsCharged						--IsCharged
			,NULL										--CoverPremium_Id
			,case										--Discriminator
				when PremiumTypeId = 'ETP' then 'E'
				when PremiumTypeId IN ('APC','APNC')
				 and PR.QuoteROId IS not null then 'S'
			end 										
		FROM dbo.UWQuoteROPremium PR
		    JOIN LatestPremium ON PR.QuoteROPremiumId=LatestPremium.QuoteROPremiumId
			JOIN @importedRiskObjects IRO ON IRO.QuoteROId = PR.QuoteROId
		WHERE PR.PremiumTypeId IN ('ETP','APC','APNC')
		UNION ALL
		-- premiums by coverage
		SELECT
			 PR.QuoteROPremiumId+@quoteIdOffSet			--Id
			,PR.QuoteId+@quoteIdOffSet					--PolicyVersion_Id
			,PR.PremiumModeId							--PremiumMode_Id
			,PR.MutualityTypeId							--Mutuality_Id
			,PR.PremiumRate								--Rate
			,PR.InstallmentPlanId						--InstalmentPlan_Id
			,PR.PremiumTypeId							--PremiumType_Id
			,PR.PremiumTrnCurrencyId					--TotalTransactionCurrency_Id
			,PR.PremiumTrnAmt							--TotalTransactionTotalValue
			,PR.PremiumClubCurrencyId					--TotalClubCurrency_Id
			,PR.PremiumClubAmt							--TotalClubTotalValue
			,PR.PremiumMembCurrencyId					--TotalMemberCurrency_Id
			,PR.PremiumMembAmt							--TotalMemberTotalValue
			,PR.BrokerageTrnCurrencyId					--BrokerageTotalTransactionCurrency_Id
			,PR.BrokerageTrnAmt							--BrokerageTotalTransactionTotalValue
			,PR.BrokerageClubCurrencyId					--BrokerageTotalClubCurrency_Id
			,PR.BrokerageClubAmt						--BrokerageTotalClubTotalValue
			,PR.BrokerageMembCurrencyId					--BrokerageTotalMemberCurrency_Id
			,PR.BrokerageMembAmt						--BrokerageTotalMemberTotalValue
			,PR.BrokeragePercentage						--BrokeragePercentage
			,PR.TaxCodeId								--TaxRate_Id
			,PR.QuoteROId+@quoteIdOffSet				--PolicyItem_Id
			,PR.LaidupRate								--LaidupRate
			,PR.CallPercentage							--CallPercentage
			,PR.MinimumDays								--MinimumDays
			,PR.ExtensionDays							--ExtensionDays
			,PR.Columbus_IsChecked						--IsChecked
			,PR.CreatedBy								--CreatedBy
			,PR.CreatedOn								--CreatedOn
			,PR.UpdatedBy								--UpdatedBy
			,PR.UpdatedOn								--UpdatedOn
			,PR.CoverageId								--GeneralCoverage_Id
			,PR.ReasonCode								--AdditionalPremiumReason_Id
			,PR.Remarks									--Remarks
			,NULL										--UWCoverPremiumId
			,PR.Columbus_BackToBack						--BackToBack
			,PR.Columbus_IsCharged						--IsCharged
			,NULL										--CoverPremium_Id
			,'C'										--Discriminator										
		FROM dbo.UWQuoteROPremium PR
		JOIN LatestPremium ON PR.QuoteROPremiumId=LatestPremium.QuoteROPremiumId
		WHERE PR.PremiumTypeId IN ('APC','APNC')
			AND PR.QuoteROId IS NULL
	END
	
---------------------------------------------------------------------------------------------------	
--  Import Brokerage
---------------------------------------------------------------------------------------------------	
	
	DECLARE @insertedBrokerages TABLE (Id INT PRIMARY KEY CLUSTERED)
	
	IF(@importBrokerages = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote Brokerages'
	
		;WITH LatestBrokerage AS
		(
			SELECT min(QuoteROPremiumBrokerId) AS BrokerageId
			FROM dbo.UWQuoteROPremiumBroker BR
			JOIN @insertedPremiums I ON BR.QuoteROPremiumId=I.Id
			GROUP BY QuoteROPremiumId,BrokerId
		)
		INSERT INTO Columbus.Brokerage
		(
			Id
		   ,Broker_Id
		   ,Premium_Id
		   ,IsMainBroker
		   ,Percentage
		   ,TransactionCurrency_Id
		   ,TransactionAmount
		   ,ClubCurrency_Id
		   ,ClubAmount
		   ,MemberCurrency_Id
		   ,MemberAmount
		   ,CreatedBy
		   ,CreatedOn
		   ,UpdatedBy
		   ,UpdatedOn
		   ,UWCoverBrokerageId
		   ,CoverBrokerage_Id
		)
		OUTPUT INSERTED.Id-@quoteIdOffSet INTO @insertedBrokerages
		SELECT
			BR.QuoteROPremiumBrokerId+@quoteIdOffSet	--Id
		   ,BR.BrokerId									--Broker_Id
		   ,BR.QuoteROPremiumId+@quoteIdOffSet			--Premium_Id
		   ,Columbus.YNToBit(BR.MainBrokerYN)			--IsMainBroker
		   ,BR.BrokeragePercentage						--Percentage
		   ,BR.BrokerageTrnCurr							--TransactionCurrency_Id
		   ,BR.BrokerageTrnAmt							--TransactionAmount
		   ,BR.BrokerageClubCurr						--ClubCurrency_Id
		   ,BR.BrokerageClubAmt							--ClubAmount
		   ,BR.BrokerageMemCurr							--MemberCurrency_Id
		   ,BR.BrokerageMemAmt							--MemberAmount
		   ,BR.CreatedBy								--CreatedBy
		   ,BR.CreatedOn								--CreatedOn
		   ,BR.UpdatedBy								--UpdatedBy
		   ,BR.UpdatedOn								--UpdatedOn
		   ,null										--UWCoverBrokerageId
		   ,null										--CoverBrokerage_Id	
		FROM LatestBrokerage
		JOIN dbo.UWQuoteROPremiumBroker BR ON LatestBrokerage.BrokerageId = BR.QuoteROPremiumBrokerId
	END
	
---------------------------------------------------------------------------------------------------	
--  Import Premium History
---------------------------------------------------------------------------------------------------	
	
	IF(@importPremiumHistories = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote Premium History'
	
		INSERT INTO Columbus.PremiumHistory
		(
			Id
		   ,Narrative
		   ,[When]
		   ,[User_Id]
		   ,Premium_Id
		)
		SELECT
			 H.Id+@quoteIdOffSet			--Id
			,H.Narrative					--Narrative
			,H.[When]						--When
			,H.[User_Id]					--User_Id
			,H.Premium_Id+@quoteIdOffSet	--Premium_Id
		FROM Columbus.QuotePremiumHistory H
		JOIN @insertedPremiums I ON H.Premium_Id=I.Id
	END
	
---------------------------------------------------------------------------------------------------	
--  Import Billing
---------------------------------------------------------------------------------------------------	
	
	IF(@importBilling = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Quote Billings'
	
		INSERT INTO Columbus.Billing
		(
			 Id
			,PolicyItem_Id
			,CoverageId
			,Organisation_Id
			,TransactionType_Id
			,TransactionTotalValue
			,TransactionCurrency_Id
			,[Days]
			,IsReversed
			,ReversedAgainstBilling_Id
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,TaxRate_Id
			,TransactionTax
			,TransactionGross
			,ClubCurrency_Id
			,ClubTotalValue
			,ClubTax
			,ClubGross
			,MemberCurrency_Id
			,MemberTotalValue
			,MemberTax
			,MemberGross
			,ClubToAmountCurrencyExchangeRate
			,ClubToMemberCurrencyExchangeRate
			,InstalmentPlan_Id
			,DebitCreditNote_Id
			,DueStart
			,DueEnd
			,IsInactive
			,EnteredTonnage
			,Rate
			,CallPercentage
			,Brokerage_Id
			,SerialNo
			,Premium_Id
			,Discriminator
			,PolicyVersion_Id
		)
		-- premiums by ship
		SELECT
			 B.QuoteBillingInfoId+@quoteIdOffSet			--Id
			,B.ROId+@quoteIdOffSet							--PolicyItem_Id
			,B.CoverageId									--CoverageId
			,coalesce(B.OrganisationId,Q.MainMemberId)		--Organisation_Id
			,B.TransactionTypeId							--TransactionType_Id
			,B.TrnCurrencyAmt								--TransactionTotalValue
			,B.TrnCurrencyId								--TransactionCurrency_Id
			,B.[Days]										--Days
			,Columbus.YNToBit(B.ReversedYN)					--IsReversed
			,B.ReversedAgainst								--ReversedAgainstBilling_Id
			,B.CreatedBy									--CreatedBy
			,B.CreatedOn									--CreatedOn
			,B.UpdatedBy									--UpdatedBy
			,B.UpdatedOn									--UpdatedOn
			,B.TaxCode										--TaxRate_Id
			,coalesce(B.TrnCurrencyTaxAmt,0)				--TransactionTax
			,B.TrnNetPremiumAmt								--TransactionGross
			,B.ClubCurrencyId								--ClubCurrency_Id
			,B.ClubCurrencyAmt								--ClubTotalValue
			,coalesce(B.ClubCurrencyTaxAmt,0)				--ClubTax
			,B.ClubNetPremiumAmt							--ClubGross
			,B.MembCurrencyId								--MemberCurrency_Id
			,B.MembCurrencyAmt								--MemberTotalValue
			,coalesce(B.MembCurrencyTaxAmt,0)				--MemberTax
			,B.MembNetPremiumAmt							--MemberGross
			,B.TrnCurrencyExchangeRate						--ClubToAmountCurrencyExchangeRate
			,B.MemberCurrencyExchangeRate					--ClubToMemberCurrencyExchangeRate
			,B.InstallmentPlanId							--InstalmentPlan_Id
			,B.DebitCreditNoteId							--DebitCreditNote_Id
			,B.DueFromDate									--DueStart
			,B.DueToDate									--DueEnd
			,Columbus.YNToBit(B.DeactiveYN)					--IsInactive
			,B.NoOfUnits									--EnteredTonnage
			,B.Rate											--Rate
			,B.CallPercentage								--CallPercentage
			,IB.Id+@quoteIdOffSet							--Brokerage_Id
			,B.SerialNo										--SerialNo
			,I.Id+@quoteIdOffSet							--Premium_Id
			,CASE
				WHEN B.QuoteROPremiumBrokerId IS NOT NULL OR GLBR.BrokerId IS NOT NULL
					THEN 'B' 
					ELSE 'P' 
			  END											--Discriminator
			,Q.QuoteId+@quoteIdOffSet
		FROM dbo.UWQuoteBillingInfo B
			JOIN @importedQuotes Q ON B.QuoteId=Q.QuoteId AND B.VersionNo=Q.VersionNo
			JOIN @importedRiskObjects IRO ON IRO.QuoteROId = B.ROId
			LEFT JOIN @insertedPremiums I ON B.QuoteROPremiumId=I.Id
			LEFT JOIN @insertedBrokerages IB ON B.QuoteROPremiumBrokerId=IB.Id
			LEFT JOIN GLBroker GLBR ON GLBR.BrokerId = B.OrganisationId
		UNION
		-- premiums by coverage
		SELECT
			 B.QuoteBillingInfoId+@quoteIdOffSet			--Id
			,B.ROId+@quoteIdOffSet							--PolicyItem_Id
			,B.CoverageId									--CoverageId
			,coalesce(B.OrganisationId,Q.MainMemberId)		--Organisation_Id
			,B.TransactionTypeId							--TransactionType_Id
			,B.TrnCurrencyAmt								--TransactionTotalValue
			,B.TrnCurrencyId								--TransactionCurrency_Id
			,B.[Days]										--Days
			,Columbus.YNToBit(B.ReversedYN)					--IsReversed
			,B.ReversedAgainst								--ReversedAgainstBilling_Id
			,B.CreatedBy									--CreatedBy
			,B.CreatedOn									--CreatedOn
			,B.UpdatedBy									--UpdatedBy
			,B.UpdatedOn									--UpdatedOn
			,B.TaxCode										--TaxRate_Id
			,coalesce(B.TrnCurrencyTaxAmt,0)				--TransactionTax
			,B.TrnNetPremiumAmt								--TransactionGross
			,B.ClubCurrencyId								--ClubCurrency_Id
			,B.ClubCurrencyAmt								--ClubTotalValue
			,coalesce(B.ClubCurrencyTaxAmt,0)				--ClubTax
			,B.ClubNetPremiumAmt							--ClubGross
			,B.MembCurrencyId								--MemberCurrency_Id
			,B.MembCurrencyAmt								--MemberTotalValue
			,coalesce(B.MembCurrencyTaxAmt,0)				--MemberTax
			,B.MembNetPremiumAmt							--MemberGross
			,B.TrnCurrencyExchangeRate						--ClubToAmountCurrencyExchangeRate
			,B.MemberCurrencyExchangeRate					--ClubToMemberCurrencyExchangeRate
			,B.InstallmentPlanId							--InstalmentPlan_Id
			,B.DebitCreditNoteId							--DebitCreditNote_Id
			,B.DueFromDate									--DueStart
			,B.DueToDate									--DueEnd
			,Columbus.YNToBit(B.DeactiveYN)					--IsInactive
			,B.NoOfUnits									--EnteredTonnage
			,B.Rate											--Rate
			,B.CallPercentage								--CallPercentage
			,IB.Id+@quoteIdOffSet							--Brokerage_Id
			,B.SerialNo										--SerialNo
			,I.Id+@quoteIdOffSet							--Premium_Id
			,CASE
				WHEN B.QuoteROPremiumBrokerId IS NOT NULL OR GLBR.BrokerId IS NOT NULL
					THEN 'B' 
					ELSE 'P' 
			  END											--Discriminator
			,Q.QuoteId+@quoteIdOffSet
		FROM dbo.UWQuoteBillingInfo B
			JOIN @importedQuotes Q ON B.QuoteId=Q.QuoteId AND B.VersionNo=Q.VersionNo
			LEFT JOIN @insertedPremiums I ON B.QuoteROPremiumId=I.Id
			LEFT JOIN @insertedBrokerages IB ON B.QuoteROPremiumBrokerId=IB.Id
			LEFT JOIN GLBroker GLBR ON GLBR.BrokerId = B.OrganisationId
		WHERE
			B.ROId IS NULL
	END
END
GO

-------------------------------------------------------------------------------------------------------------------------------------------
-- StoredProcedure: CoverPolicyDataMigration
-------------------------------------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Columbus.CoverPolicyDataMigration') AND type in (N'P', N'PC'))
	DROP PROCEDURE Columbus.CoverPolicyDataMigration
GO

CREATE PROCEDURE Columbus.CoverPolicyDataMigration
(
	@coverIdOffset INT,
	@pandiBusinessClassOffset INT,
	@defenceBusinessClassIdOffset INT,
	@crewFactor INT,
	@officerIdOffset INT,
	@coverId INT,
	@importCoverageGroups BIT,
	@importCoverages BIT,
	@importRules BIT,
	@importClauses BIT,
	@importPolicyItems BIT,
	@importJointEntrants BIT,
	@importCoAssureds BIT,
	@importMortgagees BIT,
	@importGxlCategories BIT,
	@importPremiums BIT,
	@importBrokerages BIT,
	@importPremiumHistories BIT,
	@importCrews BIT,
	@importOfficers BIT,
	@importPolicyVersionActions BIT,
	@importBilling BIT,
	@importReleaseCalls BIT,
	@importLaidUpReturns BIT
)
AS
BEGIN
--	DECLARE @maxPolicyNumber INT	
--	SELECT @maxPolicyNumber=MAX(Number) FROM Columbus.Policy

	DECLARE @importedCovers TABLE
	(
		CoverId						int,
		MainMemberId				int
		PRIMARY KEY CLUSTERED (CoverId)
	)	
	
	INSERT INTO @importedCovers
	SELECT  
		C.CoverId,
		C.MemberId
	FROM dbo.UWCover C
	WHERE @coverId IS NULL OR C.CoverId=@coverId
	
-----------------------------------------------------------------------------------------------------	
----  Import Policy
-----------------------------------------------------------------------------------------------------	
	
	EXEC Columbus.WriteLine  'Importing Cover Policies'
	
	UPDATE Columbus.Policy
	SET Club_Id = C.ClubId,
		CreatedOn = C.CreatedOn,
		CreatedBy = C.CreatedBy,
		UpdatedOn = C.UpdatedOn,
		UpdatedBy = C.UpdatedBy,
		Number = C.CoverNo
	FROM Columbus.Policy P
	JOIN @importedCovers IC ON P.Id=IC.CoverId
	JOIN dbo.UWCover C ON P.Id = C.CoverId

-----------------------------------------------------------------------------------------------------
----  Create Business Types for Policies with Multiple Types
-----------------------------------------------------------------------------------------------------	
	EXEC Columbus.WriteLine  'Splitting Cover Business Types'
	
	DECLARE @multipleBusinessTypes TABLE
	(
		CoverId			dbo.udtMasterId,
		BusinessType	nvarchar(75),
		BusinessTypeId	dbo.udtLookup
	)	

	;WITH CoversWithMultipleBusinessTypes AS
	(
		SELECT  CL.CoverId
		FROM    dbo.UWCoverClassBusinessLink CL
		JOIN	@importedCovers C ON CL.CoverId=C.CoverId
		JOIN    GLClassTypeBusinessType GL ON CL.ClassTypeBusinessTypeId = GL.ClassTypeBusinessTypeId
		GROUP BY	CL.CoverId
		HAVING count(DISTINCT GL.BusinessTypeId)>1
	)
	,CoverBusinessTypeCombinations AS	
	(
		SELECT DISTINCT CL.CoverId, BT.LookupName BusinessType, BT.LookupId BusinessTypeId
		FROM    dbo.UWCoverClassBusinessLink CL
		JOIN	CoversWithMultipleBusinessTypes CM ON CL.CoverId=CM.CoverId
		JOIN	GLClassTypeBusinessType GL ON CL.ClassTypeBusinessTypeId = GL.ClassTypeBusinessTypeId
		JOIN	dbo.GLLookup BT ON BT.LookupCategoryId = 'BUSINESSTYPE' AND GL.BusinessTypeId = BT.LookupId
	)
	-- see http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

	INSERT INTO @multipleBusinessTypes
	(
		CoverId,
		BusinessType,
		BusinessTypeId
	)		
	SELECT  DISTINCT
			Main.CoverId,
			left(Main.BusinessTypes, len(Main.BusinessTypes)-1) AS BusinessType,
			left(Main.BusinessTypeIds, len(Main.BusinessTypeIds)-1) AS BusinessTypeId
	FROM
		(
			SELECT DISTINCT		
 					 BT2.CoverId,
					 (
						SELECT BT1.BusinessType+',' AS [text()]
						FROM CoverBusinessTypeCombinations BT1
						WHERE BT1.CoverId=BT2.CoverId
						ORDER BY BT1.CoverId,BT1.BusinessType
						FOR XML PATH('')
					 ) BusinessTypes,
					 (
						SELECT BT1.BusinessTypeId+'_' AS [text()]
						FROM CoverBusinessTypeCombinations BT1
						WHERE BT1.CoverId=BT2.CoverId
						ORDER BY BT1.CoverId,BT1.BusinessType
						FOR XML PATH('')
					 ) BusinessTypeIds
			FROM CoverBusinessTypeCombinations BT2
		) Main
		
	INSERT INTO dbo.GLLookup 
	(
		LookupCategoryId,
		LookupId,
		LookupRef,
		CultureCode,
		LookupName,
		DefaultYN,
		SortOrder,
		SystemYN,
		DeactiveYN,
		CreatedBy,
		CreatedOn
	)
	SELECT DISTINCT
		'BUSINESSTYPE',
		BusinessTypeId,
		BusinessTypeId,
		'en-GB',
		BusinessType,
		'N',
		999,
		'Y',
		'Y',
		1,
		getdate()
	FROM @multipleBusinessTypes M
	WHERE NOT EXISTS (SELECT 1 FROM dbo.GLLookup WHERE LookupCategoryId='BUSINESSTYPE' AND LookupId=M.BusinessTypeId)		

-----------------------------------------------------------------------------------------------------
----  Create Business Classes for Policies with Multiple Classes
-----------------------------------------------------------------------------------------------------	
	EXEC Columbus.WriteLine  'Splitting Cover Business Classes'
	
	DECLARE @multipleBusinessClasses TABLE
	(
		CoverId			dbo.udtMasterId,
		BusinessClass	nvarchar(75),
		BusinessClassId	dbo.udtLookup
	)


	;WITH CoversWithMultipleBusinessClasses AS
	(
		SELECT  CL.CoverId
		FROM    dbo.UWCoverClassBusinessLink CL
		JOIN	@importedCovers C ON CL.CoverId=C.CoverId
		JOIN    GLClassTypeBusinessType GL ON CL.ClassTypeBusinessTypeId = GL.ClassTypeBusinessTypeId
		GROUP BY	CL.CoverId
		HAVING count(DISTINCT GL.ClassId)>1
	)
	,CoverBusinessClassCombinations AS	
	(
		SELECT DISTINCT CL.CoverId, BC.LookupName BusinessClass, BC.LookupId BusinessClassId
		FROM    dbo.UWCoverClassBusinessLink CL
		JOIN	CoversWithMultipleBusinessClasses CM ON CL.CoverId=CM.CoverId
		JOIN	GLClassTypeBusinessType GL ON CL.ClassTypeBusinessTypeId = GL.ClassTypeBusinessTypeId
		JOIN	dbo.GLLookup BC ON BC.LookupCategoryId = 'BUSINESSCLASS' AND GL.ClassId = BC.LookupId
	)

	-- see http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

	INSERT INTO @multipleBusinessClasses
	(
		CoverId,
		BusinessClass,
		BusinessClassId
	)
	SELECT  DISTINCT
			Main.CoverId,
			replace(left(Main.BusinessClasses, len(Main.BusinessClasses)-1), '&amp;', '&' ) AS BusinessClass,
			left(Main.BusinessClassIds, len(Main.BusinessClassIds)-1) AS BusinessClassId
	FROM
		(
			SELECT DISTINCT		
 					 BC2.CoverId,
					 (
						SELECT BC1.BusinessClass+',' AS [text()]
						FROM CoverBusinessClassCombinations BC1
						WHERE BC1.CoverId=BC2.CoverId
						ORDER BY BC1.CoverId,BC1.BusinessClass
						FOR XML PATH('')
					 ) BusinessClasses,
					 (
						SELECT BC1.BusinessClassId+'_' AS [text()]
						FROM CoverBusinessClassCombinations BC1
						WHERE BC1.CoverId=BC2.CoverId
						ORDER BY BC1.CoverId,BC1.BusinessClass
						FOR XML PATH('')
					 ) BusinessClassIds
			FROM CoverBusinessClassCombinations BC2
		) Main		
	
	INSERT INTO dbo.GLLookup 
	(
		LookupCategoryId,
		LookupId,
		LookupRef,
		CultureCode,
		LookupName,
		DefaultYN,
		SortOrder,
		SystemYN,
		DeactiveYN,
		CreatedBy,
		CreatedOn
	)
	SELECT DISTINCT
		'BUSINESSCLASS',
		BusinessClassId,
		BusinessClassId,
		'en-GB',
		BusinessClass,
		'N',
		999,
		'Y',
		'Y',
		1,
		getdate()
	FROM @multipleBusinessClasses M
	WHERE NOT EXISTS (SELECT 1 FROM dbo.GLLookup WHERE LookupCategoryId='BUSINESSCLASS' AND LookupId=M.BusinessClassId)		
	
-----------------------------------------------------------------------------------------------------	
----  Import PolicyVersion
-----------------------------------------------------------------------------------------------------	
	
	EXEC Columbus.WriteLine  'Importing Cover PolicyVersions'
	
	INSERT INTO Columbus.PolicyVersion
	(
		Id
	   ,Policy_Id
	   ,Broker_Id
	   ,Capacity_Id
	   ,Member_Id
	   ,PolicyStatus_Id
	   ,PolicyDateStart
	   ,PolicyDateEnd
	   ,Terms
	   ,Currency_Id
	   ,QuotaShare
	   ,IsTonnageReportable
	   ,IsUnderwriterOnly
	   ,Underwriter_Id
	   ,BusinessClass_Id
	   ,BusinessType_Id
	   ,CreatedBy
	   ,CreatedOn
	   ,UpdatedBy
	   ,UpdatedOn
	   ,VersionNumber
	   ,OrderBoundDate
	   ,DeclarationDescription
	   ,DeclarationBasis_Id
	   ,PremiumBasis_Id
	   ,PolicyDateYear
	   ,WasPreviouslyDeclined
	   ,PreviouslyDeclinedHistory
	   ,IsPremiumAccount
	   ,IsAADApplicable
	   ,CreationOrder
	)
	SELECT
		 C.CoverId+@coverIdOffSet							--Id
		,C.CoverId											--Policy_Id
		,C.BrokerId											--Broker_Id
		,CASE WHEN C.MemberCapacityId = '' THEN NULL
			ELSE C.MemberCapacityId
		END													--Capacity_Id
		,C.MemberId											--MemberId
		,'C'												--PolicyStatus_Id
		,C.CoverStartDate									--PolicyDateStart
		,C.CoverEndDate										--PolicyDateEnd
		,C.EnquiryDescription								--Terms
		,C.CoverCurrencyId									--Currency_Id
		,C.QuotaShareDescription							--QuotaShare
		,C.Columbus_IsTonnageReportable						--IsTonnageReportable
		,1													--IsUnderwriterOnly
		,UW.UserId											--Underwriter_Id
		,COALESCE(MBC.BusinessClassId, BCT.BusinessClassId)	--BusinessClass_Id
		,COALESCE(MBT.BusinessTypeId, BCT.BusinessTypeId)	--BusinessType_Id
		,C.CreatedBy										--CreatedBy
		,C.CreatedOn										--CreatedOn
		,C.UpdatedBy										--UpdatedBy
		,C.UpdatedOn										--UpdatedOn
		,0													--VersionNumber
		,C.Columbus_OrderBoundDate							--OrderBoundDate
		,C.Columbus_DeclarationDescription					--DeclarationDescription
		,C.Columbus_DeclarationBasis_Id						--DeclarationBasis_Id
		,C.Columbus_PremiumBasis_Id							--PremiumBasis_Id
		,C.PolicyYearId										--PolicyDateYear
		,Columbus.YNToBit(C.PNIYN)							--WasPreviouslyDeclined
		,C.PNIDescription									--PreviouslyDeclinedHistory
		,Columbus.YNToBit(C.PremiumAccountYN)				--IsPremiumAccount
		,Columbus.YNToBit(C.AADApplicableYN)				--IsAADApplicable
		,1													--CreationOrder
	FROM @importedCovers I
	JOIN dbo.UWCover C ON C.CoverId = I.CoverId
	LEFT JOIN dbo.GLCaseOwners UW ON C.CoverId = UW.ObjectId
								 AND UW.BusinessFunction = 'Underwriting'
								 AND UW.BusinessActivity = 'BindQuote'
	LEFT JOIN @multipleBusinessClasses MBC ON MBC.CoverId=C.CoverId
	LEFT JOIN @multipleBusinessTypes MBT ON MBT.CoverId=C.CoverId
	OUTER APPLY (
					SELECT  TOP 1 
							BC.LookupId BusinessClassId,
							BT.LookupId BusinessTypeId
					   FROM dbo.UWCoverClassBusinessLink CL
					   JOIN dbo.GLClassTypeBusinessType GL ON CL.ClassTypeBusinessTypeId=GL.ClassTypeBusinessTypeId
					   JOIN dbo.GLLookup BC ON GL.ClassId=BC.LookupId AND BC.LookupCategoryId='BUSINESSCLASS'
					   JOIN dbo.GLLookup BT ON GL.BusinessTypeId=BT.LookupId AND BT.LookupCategoryId='BUSINESSTYPE'
					   WHERE CL.CoverId=C.CoverId
				) BCT
					     
	UPDATE Columbus.Policy
	SET	CurrentPolicyVersion_Id = Id+@coverIdOffSet
	FROM Policy P
	JOIN @importedCovers I ON P.Id=I.CoverId
	
-----------------------------------------------------------------------------------------------------	
----  Import CoverageGroup
-----------------------------------------------------------------------------------------------------	
	
	IF(@importCoverageGroups = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover CoverageGroups'
		
		-- Import records missing into CoverCoverageGroup
		
		DECLARE @maxCoverageGroupId INT
		SELECT @maxCoverageGroupId=max(Id) FROM Columbus.CoverCoverageGroup
			
		INSERT INTO Columbus.CoverCoverageGroup
		(
			 Id
			,CoverageGroupNumber
			,PolicyVersion_Id
			,PolicyVersion_Version
			,ReinsuranceExceptionReason
			,ReinsuranceExceptionType_Id
			,CreatedOn
			,CreatedBy
			,UpdatedOn
			,UpdatedBy
		)
		SELECT 
			 row_number() OVER (ORDER BY RO.CoverId)+@maxCoverageGroupId	--Id
			,RO.CoverageGroupNo											    --CoverageGroupNumber
			,RO.CoverId													    --PolicyVersion_Id
			,0															    --PolicyVersion_Version
			,null														    --ReinsuranceExceptionReason
			,1															    --ReinsuranceExceptionType_Id
			,RO.CreatedOn												    --CreatedOn
			,RO.CreatedBy												    --CreatedBy
			,RO.UpdatedOn												    --UpdatedOn
			,RO.UpdatedBy												    --UpdatedBy
		FROM    
		UWCoverRO RO
        JOIN @importedCovers C ON RO.CoverId=C.CoverId
		LEFT JOIN dbo.UWCoverROShip ROSH ON RO.RODetailsId = ROSH.CoverROShipId
										    AND RO.Columbus_PolicyItemType_Id=1
		WHERE (RO.Columbus_PolicyItemType_Id=2 OR ROSH.CoverROShipId IS NOT NULL)
		and	NOT EXISTS (SELECT 1 
			              FROM Columbus.CoverCoverageGroup CG 
			             WHERE CG.PolicyVersion_Id=RO.CoverId 
			               AND CG.CoverageGroupNumber=RO.CoverageGroupNo)
		GROUP BY
			 RO.CoverageGroupNo
			,RO.CoverId		
			,RO.CreatedOn	
			,RO.CreatedBy	
			,RO.UpdatedOn	
			,RO.UpdatedBy	
			               
			               
		INSERT INTO Columbus.CoverageGroup
		(
			 Id
			,CoverageGroupNumber
			,PolicyVersion_Id
			,ReinsuranceExceptionReason
			,ReinsuranceExceptionType_Id
			,CreatedOn
			,CreatedBy
			,UpdatedOn
			,UpdatedBy
		)
		SELECT
			 CCG.Id+@coverIdOffSet					--Id
			,CCG.CoverageGroupNumber				--CoverageGroupNumber
			,CCG.PolicyVersion_Id+@coverIdOffSet	--PolicyVersion_Id
			,CCG.ReinsuranceExceptionReason			--ReinsuranceExceptionReason
			,CCG.ReinsuranceExceptionType_Id		--ReinsuranceExceptionType_Id
			,CCG.CreatedOn							--CreatedOn
			,CCG.CreatedBy							--CreatedBy
			,CCG.UpdatedOn							--UpdatedOn
			,CCG.UpdatedBy							--UpdatedBy
		FROM	Columbus.CoverCoverageGroup CCG	
		JOIN	@importedCovers C ON CCG.PolicyVersion_Id=C.CoverId
		

		
		
	END
	
-----------------------------------------------------------------------------------------------------	
----  Import Coverages
-----------------------------------------------------------------------------------------------------	
	
	DECLARE @importedCoverages TABLE
	(
		CoverageId INT PRIMARY KEY
	)
	
	IF(@importCoverages = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover Coverages'
			
		DECLARE @distinctCoverages TABLE
		(
			CoverId INT,
			CoverageGroupNo INT,
			CoverageId INT
		)
		
		DECLARE @mostRecentCoverages TABLE
		(
			CoverROCoverageLinkId INT PRIMARY KEY,
			CoverageGroupId INT
		)
			
		INSERT INTO @distinctCoverages
		(
			CoverId, 
			CoverageGroupNo, 
			CoverageId
		)
		SELECT DISTINCT CoverId, 
			CoverageGroupNo, 
			CoverageId
		FROM dbo.UWCoverROCoverageLink
		
		INSERT INTO @mostRecentCoverages
		(
			CoverROCoverageLinkId,
			CoverageGroupId
		)
		SELECT MostRecentCoverages.CoverROCoverageLinkId
			,CCG.Id
		FROM @importedCovers C
			JOIN Columbus.CoverCoverageGroup CCG ON CCG.PolicyVersion_Id = C.CoverId
			JOIN @distinctCoverages DC ON DC.CoverId = C.CoverId 
				AND DC.CoverageGroupNo = CCG.CoverageGroupNumber
			OUTER APPLY 
			(
				SELECT TOP 1 LINK.CoverROCoverageLinkId
				FROM UWCoverROCoverageLink LINK
				WHERE LINK.CoverId = DC.CoverId 
					AND LINK.CoverageGroupNo = DC.CoverageGroupNo
					AND LINK.CoverageId = DC.CoverageId
				ORDER BY COALESCE(LINK.CreatedOn, LINK.UpdatedOn) DESC, LINK.CoverROCoverageLinkId DESC
			) AS MostRecentCoverages
		
		INSERT INTO Columbus.Coverage
		(
			 Id
			,CoverageGroup_Id
			,GeneralCoverage_Id
			,IsIncluded
			,IsPoolable
			,ReinsuranceIndicatorId
			,IsLimitOfLiabilityAsPerRules
			,LimitOfLiabilityCurrencyId
			,LimitOfLiabilityAmount
			,IsDeductibleAsPerRules
			,DeductibleCurrencyId
			,DeductibleAmount
			,NarrativeTextId
			,IsIncludedInETP
			,PremiumById
			,Excess
			,ExcessCurrencyId
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,UWCoverCoverageId
			,CoverCoverage_Id
		)
		OUTPUT INSERTED.Id - @coverIdOffset INTO @importedCoverages
		SELECT 
			 COV.CoverROCoverageLinkId+@coverIdOffSet				--Id
			,[@mostRecentCoverages].CoverageGroupId+@coverIdOffSet	--CoverageGroup_Id
			,COV.CoverageId											--GeneralCoverage_Id
			,Columbus.YNToBit(COV.IncludeExcludeInd)				--IsIncluded
			,Columbus.YNToBit(COV.PoolableYN)						--IsPoolable
			,COV.ReinsuranceInd										--ReinsuranceIndicatorId
			,Columbus.YNToBit(COV.AsPerRulesLimitOfLiabilityYN)		--IsLimitOfLiabilityAsPerRules
			,COV.LOLTrnCurrencyId									--LimitOfLiabilityCurrencyId
			,COV.LOLTrnAmt											--LimitOfLiabilityAmount
			,Columbus.YNToBit(COV.AsPerRulesDeductibleAmountYN)		--IsDeductibleAsPerRules
			,COV.DeductibleTrnCurrencyId							--DeductibleCurrencyId
			,COV.DeductibleTrnAmt									--DeductibleAmount
			,COV.NarrativeTextId									--NarrativeTextId
			,Columbus.YNToBit(COV.IncludedInETPYN)					--IsIncludedInETP
			,COV.PremiumBy											--PremiumById
			,COV.Columbus_Excess		        					--Excess
			,COV.Columbus_ExcessCurrencyId							--ExcessCurrencyId
			,COV.CreatedBy											--CreatedBy
			,COV.CreatedOn											--CreatedOn
			,COV.UpdatedBy											--UpdatedBy
			,COV.UpdatedOn											--UpdatedOn
			,NULL													--UWCoverCoverageId
			,NULL													--CoverCoverage_Id
		FROM @mostRecentCoverages 
			JOIN UWCoverROCoverageLink COV on COV.CoverROCoverageLinkId = [@mostRecentCoverages].CoverROCoverageLinkId
	END
	
-----------------------------------------------------------------------------------------------------	
----  Import Rules
-----------------------------------------------------------------------------------------------------	
	DECLARE @importedRules TABLE
	(
		RuleId INT PRIMARY KEY
	)
	
	IF(@importRules = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover Rules'
		
		INSERT INTO Columbus.[Rule]
		(
			 Id
			,GeneralRule_Id
			,Coverage_Id
			,IsIncluded
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,UWCoverRuleId
			,CoverRule_Id
		)
		OUTPUT INSERTED.Id - @coverIdOffset INTO @importedRules
		SELECT 
			 RUL.CoverROCoverageRuleLinkId+@coverIdOffSet			--Id
			,RUL.RuleId												--RuleId
			,RUL.CoverROCoverageLinkId+@coverIdOffSet				--Coverage_Id
			,Columbus.YNToBit(RUL.IncludeYN)						--IsIncluded
			,RUL.CreatedBy											--CreatedBy
			,RUL.CreatedOn											--CreatedOn
			,RUL.UpdatedBy											--UpdatedBy
			,RUL.UpdatedOn											--UpdatedOn
			,null													--UWCoverRuleId
			,null													--CoverRule_Id
		FROM dbo.UWCoverROCoverageRuleLink RUL
			JOIN @importedCoverages COV ON RUL.CoverROCoverageLinkId = COV.CoverageId
			
	END
	
-----------------------------------------------------------------------------------------------------	
----  Import Clauses
-----------------------------------------------------------------------------------------------------	
	
	IF(@importClauses = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover Clauses'
		
		INSERT INTO Columbus.Clause
		(
			 Id
			,GeneralClause_Id
			,Rule_Id
			,IsPrinted
			,ClauseIncludeIndicatorId
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,UWCoverClauseId
			,CoverClause_Id
		)
		SELECT
			CL.CoverROCoverageRuleClauseLinkId+@coverIdOffSet	--Id
		   ,CL.ClauseId											--ClauseId
		   ,CL.CoverROCoverageRuleLinkId+@coverIdOffSet			--Rule_Id
		   ,Columbus.YNToBit(CL.PrintYN)						--IsPrinted
		   ,CL.IncludeInd										--ClauseIncludeIndicatorId
		   ,CL.CreatedBy										--CreatedBy
		   ,CL.CreatedOn										--CreatedOn
		   ,CL.UpdatedBy										--UpdatedBy
		   ,CL.UpdatedOn										--UpdatedOn
		   ,null												--UWCoverClauseId
		   ,null												--CoverClause_Id
		FROM dbo.UWCoverROCoverageRuleClauseLink CL
			JOIN @importedRules RUL ON CL.CoverROCoverageRuleLinkId=RUL.RuleId
	END    
-----------------------------------------------------------------------------------------------------	
----  Import PolicyItem
-----------------------------------------------------------------------------------------------------	
	
	DECLARE @riskPeriodFactor INT
	SET @riskPeriodFactor = 1000000
	
	DECLARE @distinctRiskPeriods TABLE
	(
		CoverROId INT,
		OnRiskDate	DATETIME,
		OffRiskDate DATETIME
		UNIQUE CLUSTERED (CoverROId, OnRiskDate, OffRiskDate)
	)
	
	DECLARE @importedRiskObjects TABLE
	(
		CoverROId INT PRIMARY KEY
	)
	
	IF(@importPolicyItems = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover PolicyItems'
		DECLARE @maxDate DATETIME
		SELECT @maxDate = '31-DEC-2999'
		
		;With RiskDates AS
		(
			SELECT RO.CoverROId AS CoverROId,
				COALESCE(DAT.OnRiskDate, C.CoverStartDate) AS OnRiskDate, 
				COALESCE(DAT.OffRiskDate, @maxDate) AS OffRiskDate 
			FROM UWCoverRO RO
				LEFT JOIN UWCoverRORiskDates DAT ON DAT.CoverROId=RO.CoverROId
				JOIN UWCover C ON RO.CoverId=C.CoverId
	        where ro.Columbus_PolicyItemType_Id = 1
		)
		,StartTimes AS
		(
			SELECT DISTINCT CoverROId, 
				OnRiskDate
			FROM RiskDates AS O
			WHERE NOT EXISTS 
			(
				SELECT 1 
				FROM RiskDates AS I
				WHERE I.CoverROId = O.CoverROId
					AND O.OnRiskDate > I.OnRiskDate
					AND O.OnRiskDate <= I.OffRiskDate
			)
		)
		,EndTimes AS
		(
			SELECT DISTINCT CoverROId, 
				OffRiskDate
			FROM RiskDates AS O
			WHERE NOT EXISTS 
			(
				SELECT 1 
				FROM RiskDates AS I
				WHERE I.CoverROId = O.CoverROId
					AND O.OffRiskDate >= I.OnRiskDate
					AND O.OffRiskDate < I.OffRiskDate
			)
		)
		,ContiguousDateRanges AS
		(
			SELECT CoverROId,
				OnRiskDate,
				OffRiskDate=
				(
					SELECT MIN(OffRiskDate)
					FROM EndTimes ET
					WHERE ET.CoverROId = ST.CoverROId
						AND ET.OffRiskDate >= ST.OnRiskDate
				)
			FROM StartTimes ST
		)
		INSERT INTO @distinctRiskPeriods
		(
			CoverROId, 
			OnRiskDate, 
			OffRiskDate
		)
		SELECT 
			CoverROId,
			OnRiskDate,
			CASE WHEN OffRiskDate=@maxDate THEN NULL
				ELSE OffRiskDate
			END AS OffRisk
		FROM ContiguousDateRanges
		ORDER BY
			CoverROId, 
			OnRiskDate, 
			OffRiskDate
		
		-- Adding declaration items to @distinctRiskPeriods so that we can do an inner join on @distinctRiskPeriods
		-- below rather than a left outer join - makes a BIG difference in how long it takes to run.
		INSERT INTO @distinctRiskPeriods
		(
			CoverROId			
		)
		SELECT  RO.CoverROId
		FROM    UWCoverRO RO
		WHERE RO.Columbus_PolicyItemType_Id = 2
				
		;WITH InsuredParties AS
		(
			SELECT 
				CertificateMemberId,
				AccountingMemberId,
				RODetailsId,
				BusinessTypeId,
				CoverId
			FROM UWCoverROCertActgMember
			GROUP BY 	
				CertificateMemberId,
				AccountingMemberId,
				RODetailsId,
				BusinessTypeId,
				CoverId,
				CreatedBy,
				CreatedOn,
				UpdatedBy,
				UpdatedOn
		)
		INSERT INTO Columbus.PolicyItem
		(
			 Id
			,PolicyVersion_Id
			,CoverageGroup_Id
			,OnRiskDate
			,OffRiskDate
			,Ship_Id
			,EnteredTonnage
			,IsCofrRequested
			,WasPreviouslySurveyed
			,PreviousSurveyDetails
			,IsHighRisk
			,IsLaidUp
			,TradingArea_Id
			,HaveSentSurvey
			,IsSurveyRequired
			,HullValue
			,HullValueCurrencyId
			,CoverageTemplateId
			,Certificate_Id
			,CertificateMember_Id
			,AccountingMember_Id
			,OrderPercentage
			,IsNewAttachment
			,HeldCovered
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,UWCoverROPolicyItemId
			,PolicyItemTypeId
			,CoverPolicyItem_Id
		 )
		 OUTPUT INSERTED.Id-@coverIdOffSet INTO @importedRiskObjects -- will delete spurious records, where ROW_NUMBER comes into play, as next step below.
		 SELECT
			RO.CoverROId
				+((ROW_NUMBER() OVER (PARTITION BY RO.CoverROId ORDER BY DAT.OnRiskDate)-1)*@riskPeriodFactor)
				+@coverIdOffSet										--Id
		   ,RO.CoverId+@coverIdOffSet								--PolicyVersion_Id
		   ,CCG.Id+@coverIdOffSet									--CoverageGroup_Id
		   ,DAT.OnRiskDate											--OnRiskDate
		   ,DAT.OffRiskDate											--OffRiskDate
		   ,ROSH.ShipId												--Ship_Id
		   ,COALESCE(ROSH.EnteredTonnage,0)							--EnteredTonnage
		   ,COALESCE(Columbus.YNToBit(ROSH.COFRRequestedYN),0)		--IsCofrRequested
		   ,COALESCE(Columbus.YNToBit(ROSH.PNIBeforeYN),0)			--WasPreviouslySurveyed
		   ,ROSH.PNIBeforeDetails									--PreviousSurveyDetails
		   ,COALESCE(Columbus.YNToBit(ROSH.HighRiskYN),0)			--IsHighRisk
		   ,COALESCE(Columbus.YNToBit(ROSH.LaidUpIndicatorYN),0)	--IsLaidUp
		   ,ROSH.TradingAreaId										--TradingArea_Id
		   ,COALESCE(Columbus.YNToBit(ROSH.SurveySentYN),0)			--HaveSentSurvey
		   ,COALESCE(Columbus.YNToBit(ROSH.SurveyRequiredYN),0)		--IsSurveyRequired
		   ,ROSH.HullValue											--HullValue
		   ,ROSH.HullValueCurrencyId								--HullValueCurrencyId
		   ,RO.CoverageTemplateId									--CoverageTemplateId
		   ,null													--Certificate_Id: will be removed
		   ,IP.CertificateMemberId									--CertificateMember_Id
		   ,IP.AccountingMemberId									--AccountingMember_Id
		   ,RO.Columbus_OrderPercentage								--OrderPercentage
		   ,RO.Columbus_IsNewAttachment								--IsNewAttachment
		   ,null													--HeldCovered: will be removed
		   ,RO.CreatedBy											--CreatedBy
		   ,RO.CreatedOn											--CreatedOn
		   ,RO.UpdatedBy											--UpdatedBy
		   ,RO.UpdatedOn											--UpdatedOn
		   ,null													--UWCoverROPolicyItemId: will be removed
		   ,RO.Columbus_PolicyItemType_Id							--PolicyItemTypeId
		   ,null													--CoverPolicyItem_Id
		FROM dbo.UWCoverRO RO
		JOIN @importedCovers C ON RO.CoverId=C.CoverId
		LEFT JOIN dbo.UWCoverROShip ROSH ON RO.RODetailsId = ROSH.CoverROShipId
										AND RO.Columbus_PolicyItemType_Id=1
		JOIN @distinctRiskPeriods DAT ON RO.CoverROId=DAT.CoverROId
		LEFT JOIN InsuredParties IP ON RO.RODetailsId = IP.RODetailsId 
								   AND RO.BusinessTypeId = IP.BusinessTypeId
								   AND RO.CoverId = IP.CoverId
		JOIN Columbus.CoverCoverageGroup CCG ON RO.CoverId = CCG.PolicyVersion_Id
											AND RO.CoverageGroupNo = CCG.CoverageGroupNumber						       
		WHERE (RO.Columbus_PolicyItemType_Id=2 OR ROSH.CoverROShipId IS NOT NULL)
			
		-- There wasn't an easy way to OUTPUT all the original RO.CoverROIds above. e.g. if we imported records 200123456 and 201123456 from CoverROId 123456 above,
		-- @importedRiskObjects would contain 123456 (correct) but also 1123456 (meaningless). The following query will delete the meaningless 1123456. All
		-- original CoverROIds are less than about 500,000 at time of writing, so we won't delete any valid CoverROIds accidentally.
		DELETE 
		FROM @importedRiskObjects
		WHERE CoverROId >= @riskPeriodFactor
		
	END
	
-----------------------------------------------------------------------------------------------------	
----  Import Joint Entrant
-----------------------------------------------------------------------------------------------------	
	IF(@importJointEntrants = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover JointEntrants'
	
		INSERT INTO Columbus.JointEntrant
		(
			Id
		   ,PolicyItem_Id
		   ,Organisation_Id
		   ,Capacity_Id
		   ,IsPrincipalAssured
		   ,HaveReceivedConfirmation
		   ,IsDocumentAttached
		   ,CreatedBy
		   ,CreatedOn
		   ,UpdatedBy
		   ,UpdatedOn
		   ,SequenceNumber
		   ,UWCoverJointEntrantId
		   ,CoverJointEntrant_Id
		)
		SELECT
		   JE.CoverROJEId+@coverIdOffSet				--Id
		   ,RO.CoverROId+@coverIdOffSet					--PolicyItem_Id
		   ,JE.JointEntrantId							--Organisation_Id
		   ,JE.CapacityTypeId							--Capacity_Id
		   ,Columbus.YNToBit(JE.PrincipalAssuredYN)		--IsPrincipalAssured
		   ,Columbus.YNToBit(JE.ConfirmationYN)			--HaveReceivedConfirmation
		   ,Columbus.YNToBit(DocumentAttachedYN)		--IsDocumentAttached
		   ,JE.CreatedBy								--CreatedBy
		   ,JE.CreatedOn								--CreatedOn
		   ,JE.UpdatedBy								--UpdatedBy
		   ,JE.UpdatedOn								--UpdatedOn
		   ,-1											--SequenceNumber
		   ,null										--UWCoverJointEntrantId
		   ,null										--CoverJointEntrant_Id
		FROM dbo.UWCoverROJE JE 
			JOIN dbo.UWCoverRO RO ON JE.RODetailsId = RO.RODetailsId AND JE.CoverId=RO.CoverId
			JOIN @importedRiskObjects IRO ON IRO.CoverROId = RO.CoverROId
			JOIN @importedCovers C ON RO.CoverId=C.CoverId
	END
-----------------------------------------------------------------------------------------------------	
----  Import Coassured
-----------------------------------------------------------------------------------------------------	
	IF(@importCoAssureds = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover CoAssured'
		
		INSERT INTO [Columbus].[CoAssured]
			   ([Id]
			   ,[PolicyItem_Id]
			   ,[Name]
			   ,[Capacity_Id]
			   ,[CreatedBy]
			   ,[CreatedOn]
			   ,[UpdatedBy]
			   ,[UpdatedOn]
			   ,[SequenceNumber]
			   ,[UWCoverCoAssuredId]
			   ,[CoverCoAssured_Id])
		 SELECT
				COA.CoverROCoAssuredId+@coverIdOffSet
			   ,RO.CoverROId+@coverIdOffSet			--PolicyItem_Id
			   ,COA.CoAssuredName					--Name
			   ,COA.CapacityId						--Capacity_Id
			   ,COA.CreatedBy						--CreatedBy
			   ,COA.CreatedOn						--CreatedOn
			   ,COA.UpdatedBy						--UpdatedBy
			   ,COA.UpdatedOn						--UpdatedOn
			   ,COA.SequenceNo						--SequenceNumber
			   ,NULL								--UWCoverCoAssuredId
			   ,NULL								--CoverCoAssured_Id
		FROM
			dbo.UWCoverROCoAssured COA
				JOIN dbo.UWCoverRO RO ON RO.CoverId = COA.CoverId
					AND RO.RODetailsId = COA.RODetailsId
				JOIN @importedRiskObjects IRO ON IRO.CoverROId = RO.CoverROId
				JOIN @importedCovers C ON RO.CoverId=C.CoverId
	END
			
-----------------------------------------------------------------------------------------------------	
----  Import Mortgagees
-----------------------------------------------------------------------------------------------------	
	 
	 IF(@importMortgagees = 1)
	 BEGIN
		 EXEC Columbus.WriteLine  'Importing Cover Mortgagees'
		 
		 INSERT INTO [Columbus].[Mortgagee]
			   ([Id]
			   ,[PolicyItem_Id]
			   ,[Organisation_Id]
			   ,[IsFirstMortgagee]
			   ,[IsConsortiumLeader]
			   ,[EffectiveDate]
			   ,[ExpiryDate]
			   ,[CreatedBy]
			   ,[CreatedOn]
			   ,[UpdatedBy]
			   ,[UpdatedOn]
			   ,[SequenceNumber]
			   ,[UWCoverMortgageeId]
			   ,[CoverMortgagee_Id])
		 SELECT
			   CoverROMortgageesId+@CoverIdOffSet
			   ,RO.CoverROId+@CoverIdOffSet						--PolicyItem_Id
			   ,M.MortgageeId									--Organisation_Id
			   ,Columbus.YNToBit(M.FirstMortgageeYN)			--IsFirstMortgagee
			   ,Columbus.YNToBit(M.IsConsortiumYN)				--IsConsortiumLeader
			   ,M.EffectiveDate									--EffectiveDate
			   ,M.ExpiryDate									--ExpiryDate
			   ,M.CreatedBy										--CreatedBy
			   ,M.CreatedOn										--CreatedOn
			   ,M.UpdatedBy										--UpdatedBy
			   ,M.UpdatedOn										--UpdatedOn
			   ,M.SequenceNo									--SequenceNumber
			   ,NULL											--UWCoverMortgageeId
			   ,NULL											--CoverMortgagee_Id
		 FROM
			dbo.UWCoverROMortgagees M
				JOIN dbo.UWCoverRO RO ON RO.CoverId = M.CoverId AND RO.RODetailsId = M.RODetailsId
				JOIN @importedRiskObjects IRO ON IRO.CoverROId = RO.CoverROId
				JOIN @importedCovers Q ON RO.CoverId=Q.CoverId
	END
	
-----------------------------------------------------------------------------------------------------	
----  Import GXL categories
-----------------------------------------------------------------------------------------------------	
	
	IF(@importGxlCategories = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover GXLs'
		
		INSERT INTO Columbus.PolicyItemGxlCategory
			   (Id
			   ,PolicyItem_Id
			   ,GxlCategory_Id
			   ,EffectiveDate
			   ,OldCoverPolicyItemGxlCategoryId
			   ,CreatedBy
			   ,CreatedOn
			   ,UpdatedBy
			   ,UpdatedOn
			   ,CoverPolicyItemGxlCategory_Id)
		SELECT
			   G.Id+@CoverIdOffSet
			   ,G.PolicyItem_Id+@CoverIdOffSet
			   ,G.GxlCategory
			   ,G.EffectiveDate
			   ,G.CoverPolicyItemGxlCategoryId
			   ,G.CreatedBy
			   ,G.CreatedOn
			   ,G.UpdatedBy
			   ,G.UpdatedOn
			   ,NULL
		FROM
			Columbus.CoverPolicyItemGxlCategory G
				JOIN dbo.UWCoverRO RO ON G.PolicyItem_Id = RO.CoverROId
				JOIN @importedCovers Q ON RO.CoverId=Q.CoverId
				JOIN @importedRiskObjects IRO ON IRO.CoverROId = RO.CoverROId
	END
	
	
-----------------------------------------------------------------------------------------------------	
----  Import Crew
-----------------------------------------------------------------------------------------------------	
	
	IF(@importCrews = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover Crew'
		
		DECLARE @importedCrew TABLE
		(
			CoverROShipCrewId		INT
		   ,Columbus_Migrated_Id	INT
		   ,PolicyItem_Id			INT
		   ,Number					INT
		   ,Country_Id				NVARCHAR(3)
		   ,EffectiveDate			DATETIME
		   ,ExpiryDate				DATETIME
		   ,CreatedBy				INT
		   ,CreatedOn				DATETIME
		   ,UpdatedBy				INT
		   ,UpdatedOn				DATETIME
		)
		
		INSERT INTO @importedCrew
		SELECT	
			 Crew.CoverROShipCrewId
			,@coverIdOffSet
				+(CASE 
					WHEN RO.BusinessClassId='P' 
						THEN @pandiBusinessClassOffset 
					ELSE 
						@defenceBusinessClassIdOffset 
				   END)
				+(@crewFactor*
					(row_number() 
						OVER(PARTITION BY Crew.CoverROShipCrewId,RO.BusinessClassId 
					  		 ORDER BY Crew.CoverROShipCrewId))
					+Crew.CoverROShipCrewId)
		   ,RO.CoverROId+@coverIdOffSet		--PolicyItem_Id
		   ,Crew.CrewNos					--Number
		   ,Crew.CrewNationalityId			--Country_Id
		   ,Crew.EffectiveDate				--EffectiveDate
		   ,Crew.ExpiryDate					--ExpiryDate
		   ,Crew.CreatedBy					--CreatedBy
		   ,Crew.CreatedOn					--CreatedOn
		   ,Crew.UpdatedBy					--UpdatedBy
		   ,Crew.UpdatedOn					--UpdatedOn			
		FROM dbo.UWCoverRO RO
			JOIN @importedRiskObjects IRO ON IRO.CoverROId = RO.CoverROId
			JOIN @importedCovers C ON RO.CoverId=C.CoverId
			JOIN dbo.UWCoverROShip ROSH ON RO.RODetailsId = ROSH.CoverROShipId
			JOIN UWCoverROShipCrewDetails Crew ON Crew.CoverROShipId = ROSH.CoverROShipId
			WHERE RO.Columbus_PolicyItemType_Id = 1

		UPDATE Crew
		SET Crew.Columbus_Migrated_Id = iCrew.Columbus_Migrated_Id
		FROM UWCoverROShipCrewDetails Crew
		JOIN @importedCrew iCrew ON Crew.CoverROShipCrewId=iCrew.CoverROShipCrewId

		INSERT INTO Columbus.PolicyItemCompany
		   (Id
		   ,PolicyItem_Id
		   ,Type
		   ,Number
		   ,Country_Id
		   ,EffectiveDate
		   ,ExpiryDate
		   ,CreatedBy
		   ,CreatedOn
		   ,UpdatedBy
		   ,UpdatedOn
		   ,UWCoverCompanyId
		   ,CoverPolicyItemCompany_Id)
		SELECT
			Columbus_Migrated_Id		--Id
		   ,PolicyItem_Id				--PolicyItem_Id
		   ,'C'							--Type
		   ,Number						--Number
		   ,Country_Id					--Country_Id
		   ,EffectiveDate				--EffectiveDate
		   ,ExpiryDate					--ExpiryDate
		   ,CreatedBy					--CreatedBy
		   ,CreatedOn					--CreatedOn
		   ,UpdatedBy					--UpdatedBy
		   ,UpdatedOn					--UpdatedOn
		   ,NULL						--UWCoverCompanyId
		   ,NULL						--CoverPolicyItemCompany_Id
		FROM @importedCrew
		ORDER BY Columbus_Migrated_Id
	END
	
-----------------------------------------------------------------------------------------------------	
----  Import Officers
-----------------------------------------------------------------------------------------------------	
		
	IF(@importOfficers = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover Officers'
		
		DECLARE @importedOfficers TABLE
		(
			CoverROShipOfficerId	INT
		   ,Columbus_Migrated_Id	INT
		   ,PolicyItem_Id			INT
		   ,Number					INT
		   ,Country_Id				NVARCHAR(3)
		   ,EffectiveDate			DATETIME
		   ,ExpiryDate				DATETIME
		   ,CreatedBy				INT
		   ,CreatedOn				DATETIME
		   ,UpdatedBy				INT
		   ,UpdatedOn				DATETIME
		)
		
		INSERT INTO @importedOfficers
		SELECT
			O.CoverROShipOfficerId
		   ,@coverIdOffSet
				+@officerIdOffset
				+(CASE 
					WHEN RO.BusinessClassId='P' 
						THEN @pandiBusinessClassOffset 
					ELSE 
						@defenceBusinessClassIdOffset 
					END)
				+(@crewFactor*(row_number() OVER(PARTITION BY O.CoverROShipOfficerId,RO.BusinessClassId 
				                                 ORDER BY O.CoverROShipOfficerId))
					+O.CoverROShipOfficerId)
           ,RO.CoverROId+@coverIdOffSet			--PolicyItem_Id
           ,O.OfficerNos						--Number
           ,O.OfficerNationalityId				--Country_Id
           ,O.EffectiveDate						--EffectiveDate
           ,O.ExpiryDate						--ExpiryDate
           ,O.CreatedBy							--CreatedBy
           ,O.CreatedOn							--CreatedOn
           ,O.UpdatedBy							--UpdatedBy
           ,O.UpdatedOn							--UpdatedOn
		FROM
			dbo.UWCoverRO RO
			JOIN @importedRiskObjects IRO ON IRO.CoverROId = RO.CoverROId
			JOIN @importedCovers Q ON RO.CoverId=Q.CoverId
			JOIN dbo.UWCoverROShip ROSH ON RO.RODetailsId = ROSH.CoverROShipId
			JOIN dbo.UWCoverROShipOfficerDetails O ON O.CoverROShipId = ROSH.CoverROShipId
		WHERE
			RO.Columbus_PolicyItemType_Id = 1
			
		UPDATE Officers
		SET Officers.Columbus_Migrated_Id = iOff.Columbus_Migrated_Id
		FROM UWCoverROShipOfficerDetails Officers
		JOIN @importedOfficers iOff ON Officers.CoverROShipOfficerId=iOff.CoverROShipOfficerId		
		
		INSERT INTO Columbus.PolicyItemCompany
		   (Id
		   ,PolicyItem_Id
		   ,Type
		   ,Number
		   ,Country_Id
		   ,EffectiveDate
		   ,ExpiryDate
		   ,CreatedBy
		   ,CreatedOn
		   ,UpdatedBy
		   ,UpdatedOn
		   ,UWCoverCompanyId
		   ,CoverPolicyItemCompany_Id)
		SELECT
			Columbus_Migrated_Id		--Id
		   ,PolicyItem_Id				--PolicyItem_Id
		   ,'O'							--Type
		   ,Number						--Number
		   ,Country_Id					--Country_Id
		   ,EffectiveDate				--EffectiveDate
		   ,ExpiryDate					--ExpiryDate
		   ,CreatedBy					--CreatedBy
		   ,CreatedOn					--CreatedOn
		   ,UpdatedBy					--UpdatedBy
		   ,UpdatedOn					--UpdatedOn
		   ,NULL						--UWCoverCompanyId
		   ,NULL						--CoverPolicyItemCompany_Id
		FROM @importedOfficers
		ORDER BY Columbus_Migrated_Id			
	END
	
-----------------------------------------------------------------------------------------------------	
----  Import Premiums
-----------------------------------------------------------------------------------------------------	

	DECLARE @insertedPremiums TABLE (Id INT PRIMARY KEY CLUSTERED)
	
	IF(@importPremiums = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover Premiums'
		
		;WITH DistinctPremiums AS
		(
			SELECT PremiumTypeId,CoverId,CoverROId,CoverageId
			FROM UWCoverROPremium P
			WHERE P.PremiumTypeId IN ('ETP','APC','APNC')
			GROUP BY PremiumTypeId,CoverId,CoverROId,CoverageId
		)
		, LatestPremium AS
		(
			SELECT LatestPremium.CoverROPremiumId
			FROM DistinctPremiums
			OUTER APPLY 
			(	
				SELECT TOP 1 P.CoverROPremiumId
				FROM UWCoverROPremium P 
				WHERE P.PremiumTypeId=DistinctPremiums.PremiumTypeId
				  AND P.CoverId=DistinctPremiums.CoverId
				  AND (P.CoverROId=DistinctPremiums.CoverROId OR (P.CoverROId IS NULL AND DistinctPremiums.CoverROId IS NULL))
				  AND (P.CoverageId=DistinctPremiums.CoverageId OR (P.CoverageId IS NULL AND DistinctPremiums.CoverageId IS NULL))
				ORDER BY coalesce(P.UpdatedOn, P.CreatedOn) DESC, P.CoverROPremiumId DESC
			) AS LatestPremium
		)
		INSERT INTO Columbus.Premium
		(
			Id
			,PolicyVersion_Id
			,PremiumMode_Id
			,Mutuality_Id
			,Rate
			,InstalmentPlan_Id
			,PremiumType_Id
			,TotalTransactionCurrency_Id
			,TotalTransactionTotalValue
			,TotalClubCurrency_Id
			,TotalClubTotalValue
			,TotalMemberCurrency_Id
			,TotalMemberTotalValue
			,BrokerageTotalTransactionCurrency_Id
			,BrokerageTotalTransactionTotalValue
			,BrokerageTotalClubCurrency_Id
			,BrokerageTotalClubTotalValue
			,BrokerageTotalMemberCurrency_Id
			,BrokerageTotalMemberTotalValue
			,BrokeragePercentage
			,TaxRate_Id
			,PolicyItem_Id
			,LaidupRate
			,CallPercentage
			,MinimumDays
			,ExtensionDays
			,IsChecked
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,GeneralCoverage_Id
			,AdditionalPremiumReason_Id
			,Remarks
			,UWCoverPremiumId
			,BackToBack
			,IsCharged
			,CoverPremium_Id
			,Discriminator
		)
		OUTPUT INSERTED.Id-@coverIdOffSet INTO @insertedPremiums
			-- premiums by ship by coverage or just by ship (ETP) - only if the related RO was imported.
			SELECT
				 PR.CoverROPremiumId+@coverIdOffSet					--Id
				,PR.CoverId+@coverIdOffSet							--PolicyVersion_Id
				,COALESCE(PR.PremiumModeId,'PRO') AS PremiumModeId	--PremiumMode_Id
				,PR.MutualityTypeId									--Mutuality_Id
				,PR.PremiumRate										--Rate
				,PR.InstallmentPlanId								--InstalmentPlan_Id
				,PR.PremiumTypeId									--PremiumType_Id
				,PR.PremiumTrnCurrencyId							--TotalTransactionCurrency_Id
				,PR.PremiumTrnAmt									--TotalTransactionTotalValue
				,PR.PremiumClubCurrencyId							--TotalClubCurrency_Id
				,PR.PremiumClubAmt									--TotalClubTotalValue
				,PR.PremiumMembCurrencyId							--TotalMemberCurrency_Id
				,PR.PremiumMembAmt									--TotalMemberTotalValue
				,PR.BrokerageTrnCurrencyId							--BrokerageTotalTransactionCurrency_Id
				,PR.BrokerageTrnAmt									--BrokerageTotalTransactionTotalValue
				,PR.BrokerageClubCurrencyId							--BrokerageTotalClubCurrency_Id
				,PR.BrokerageClubAmt								--BrokerageTotalClubTotalValue
				,PR.BrokerageMembCurrencyId							--BrokerageTotalMemberCurrency_Id
				,PR.BrokerageMembAmt								--BrokerageTotalMemberTotalValue
				,PR.BrokeragePercentage								--BrokeragePercentage
				,CASE WHEN PR.TaxCodeId	= '' 
					THEN NULL 
					ELSE PR.TaxCodeId 
				END													--TaxRate_Id
				,PR.CoverROId+@coverIdOffSet						--PolicyItem_Id
				,PR.LaidupRate										--LaidupRate
				,PR.CallPercentage									--CallPercentage
				,PR.MinimumDays										--MinimumDays
				,PR.ExtensionDays									--ExtensionDays
				,PR.Columbus_IsChecked								--IsChecked
				,PR.CreatedBy										--CreatedBy
				,PR.CreatedOn										--CreatedOn
				,PR.UpdatedBy										--UpdatedBy
				,PR.UpdatedOn										--UpdatedOn
				,PR.CoverageId										--GeneralCoverage_Id
				,PR.ReasonCode										--AdditionalPremiumReason_Id
				,PR.Remarks											--Remarks
				,NULL												--UWCoverPremiumId
				,PR.Columbus_BackToBack								--BackToBack
				,PR.Columbus_IsCharged								--IsCharged
				,NULL												--CoverPremium_Id
				,CASE												--Discriminator
					WHEN PremiumTypeId = 'ETP' THEN 'E'
					WHEN PremiumTypeId IN ('APC','APNC')
						AND PR.CoverROId IS NOT NULL THEN 'S'
				END
			FROM LatestPremium 
			    JOIN dbo.UWCoverROPremium PR ON PR.CoverROPremiumId = LatestPremium.CoverROPremiumId
				JOIN @importedCovers C ON PR.CoverId=C.CoverId
				JOIN @importedRiskObjects IRO ON PR.CoverROId = IRO.CoverROId
			WHERE PR.PremiumTypeId IN ('ETP','APC','APNC')
		UNION
			-- premiums by coverage
			SELECT PR.CoverROPremiumId+@coverIdOffSet					--Id
				,PR.CoverId+@coverIdOffSet								--PolicyVersion_Id
				,COALESCE(PR.PremiumModeId,'PRO') AS PremiumModeId		--PremiumMode_Id
				,PR.MutualityTypeId										--Mutuality_Id
				,PR.PremiumRate											--Rate
				,PR.InstallmentPlanId									--InstalmentPlan_Id
				,PR.PremiumTypeId										--PremiumType_Id
				,PR.PremiumTrnCurrencyId								--TotalTransactionCurrency_Id
				,PR.PremiumTrnAmt										--TotalTransactionTotalValue
				,PR.PremiumClubCurrencyId								--TotalClubCurrency_Id
				,PR.PremiumClubAmt										--TotalClubTotalValue
				,PR.PremiumMembCurrencyId								--TotalMemberCurrency_Id
				,PR.PremiumMembAmt										--TotalMemberTotalValue
				,PR.BrokerageTrnCurrencyId								--BrokerageTotalTransactionCurrency_Id
				,PR.BrokerageTrnAmt										--BrokerageTotalTransactionTotalValue
				,PR.BrokerageClubCurrencyId								--BrokerageTotalClubCurrency_Id
				,PR.BrokerageClubAmt									--BrokerageTotalClubTotalValue
				,PR.BrokerageMembCurrencyId								--BrokerageTotalMemberCurrency_Id
				,PR.BrokerageMembAmt									--BrokerageTotalMemberTotalValue
				,PR.BrokeragePercentage									--BrokeragePercentage
				,CASE WHEN PR.TaxCodeId	= '' 
					THEN NULL 
					ELSE PR.TaxCodeId 
				END														--TaxRate_Id
				,PR.CoverROId+@coverIdOffSet							--PolicyItem_Id
				,PR.LaidupRate											--LaidupRate
				,PR.CallPercentage										--CallPercentage
				,PR.MinimumDays											--MinimumDays
				,PR.ExtensionDays										--ExtensionDays
				,PR.Columbus_IsChecked									--IsChecked
				,PR.CreatedBy											--CreatedBy
				,PR.CreatedOn											--CreatedOn
				,PR.UpdatedBy											--UpdatedBy
				,PR.UpdatedOn											--UpdatedOn
				,PR.CoverageId											--GeneralCoverage_Id
				,PR.ReasonCode											--AdditionalPremiumReason_Id
				,PR.Remarks												--Remarks
				,NULL													--UWCoverPremiumId
				,PR.Columbus_BackToBack									--BackToBack
				,PR.Columbus_IsCharged									--IsCharged
				,NULL													--CoverPremium_Id
				,'C'													--Discriminator
			FROM LatestPremium 
			    JOIN dbo.UWCoverROPremium PR ON PR.CoverROPremiumId = LatestPremium.CoverROPremiumId
				JOIN @importedCovers C ON PR.CoverId=C.CoverId
			WHERE PR.PremiumTypeId IN ('APC','APNC')
				AND PR.CoverROId IS NULL
			
	END
	
-----------------------------------------------------------------------------------------------------	
----  Import Brokerage
-----------------------------------------------------------------------------------------------------	
	
	DECLARE @insertedBrokerages TABLE (Id INT PRIMARY KEY CLUSTERED)
	
	IF(@importBrokerages = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover Brokerages'
	
		;WITH LatestBrokerage AS
		(
			SELECT min(CoverROPremiumBrokerId) AS BrokerageId
			FROM dbo.UWCoverROPremiumBroker BR
			JOIN @insertedPremiums I ON BR.CoverROPremiumId=I.Id
			GROUP BY CoverROPremiumId,BrokerId
		)
		INSERT INTO Columbus.Brokerage
		(
			Id
		   ,Broker_Id
		   ,Premium_Id
		   ,IsMainBroker
		   ,Percentage
		   ,TransactionCurrency_Id
		   ,TransactionAmount
		   ,ClubCurrency_Id
		   ,ClubAmount
		   ,MemberCurrency_Id
		   ,MemberAmount
		   ,CreatedBy
		   ,CreatedOn
		   ,UpdatedBy
		   ,UpdatedOn
		   ,UWCoverBrokerageId
		   ,CoverBrokerage_Id
		)
		OUTPUT INSERTED.Id-@coverIdOffSet INTO @insertedBrokerages
		SELECT
			BR.CoverROPremiumBrokerId+@coverIdOffSet	--Id
		   ,BR.BrokerId									--Broker_Id
		   ,BR.CoverROPremiumId+@coverIdOffSet			--Premium_Id
		   ,Columbus.YNToBit(BR.MainBrokerYN)			--IsMainBroker
		   ,BR.BrokeragePercentage						--Percentage
		   ,BR.BrokerageTrnCurr							--TransactionCurrency_Id
		   ,BR.BrokerageTrnAmt							--TransactionAmount
		   ,BR.BrokerageClubCurr						--ClubCurrency_Id
		   ,BR.BrokerageClubAmt							--ClubAmount
		   ,BR.BrokerageMemCurr							--MemberCurrency_Id
		   ,BR.BrokerageMemAmt							--MemberAmount
		   ,BR.CreatedBy								--CreatedBy
		   ,BR.CreatedOn								--CreatedOn
		   ,BR.UpdatedBy								--UpdatedBy
		   ,BR.UpdatedOn								--UpdatedOn
		   ,NULL										--UWCoverBrokerageId
		   ,NULL										--CoverBrokerage_Id	
		FROM dbo.UWCoverROPremiumBroker BR
		JOIN LatestBrokerage ON BR.CoverROPremiumBrokerId=LatestBrokerage.BrokerageId
	END
	
-----------------------------------------------------------------------------------------------------	
----  Import Premium History
-----------------------------------------------------------------------------------------------------	
	
--	Premium histories are never created on cover
	
-----------------------------------------------------------------------------------------------------	
----  Import Billing
-----------------------------------------------------------------------------------------------------	
	
	IF(@importBilling = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Cover Billings'
	
		INSERT INTO Columbus.Billing
		(
			 Id
			,PolicyItem_Id
			,CoverageId
			,Organisation_Id
			,TransactionType_Id
			,TransactionTotalValue
			,TransactionCurrency_Id
			,[Days]
			,IsReversed
			,ReversedAgainstBilling_Id
			,CreatedBy
			,CreatedOn
			,UpdatedBy
			,UpdatedOn
			,TaxRate_Id
			,TransactionTax
			,TransactionGross
			,ClubCurrency_Id
			,ClubTotalValue
			,ClubTax
			,ClubGross
			,MemberCurrency_Id
			,MemberTotalValue
			,MemberTax
			,MemberGross
			,ClubToAmountCurrencyExchangeRate
			,ClubToMemberCurrencyExchangeRate
			,InstalmentPlan_Id
			,DebitCreditNote_Id
			,LegacyDocumentNumber
			,DueStart
			,DueEnd
			,IsInactive
			,EnteredTonnage
			,Rate
			,CallPercentage
			,Brokerage_Id
			,SerialNo
			,Premium_Id
			,Discriminator
			,PolicyVersion_Id
		)
		-- Insert all billings for premiums by ship
		SELECT
			 B.BillingInfoId+@coverIdOffSet					--Id
			,B.ROId+@coverIdOffSet							--PolicyItem_Id
			,B.CoverageId									--CoverageId
			,coalesce(B.OrganisationId,C.MainMemberId)		--Organisation_Id
			,B.TransactionTypeId							--TransactionType_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.TrnCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--TransactionTotalValue
			,B.TrnCurrencyId								--TransactionCurrency_Id
			,B.[Days]										--Days
			,Columbus.YNToBit(B.ReversedYN)					--IsReversed
			,B.ReversedAgainst+@coverIdOffset																							--ReversedAgainstBilling_Id
			,CASE WHEN B.CreatedBy IS NULL THEN 1
				WHEN B.CreatedBy = 0 THEN 1
				ELSE B.CreatedBy 
			END																															--CreatedBy
			,B.CreatedOn									--CreatedOn
			,CASE WHEN B.UpdatedBy IS NULL THEN NULL
				WHEN B.UpdatedBy = 0 THEN 1
				ELSE B.UpdatedBy
			END																															--UpdatedBy
			,B.UpdatedOn									--UpdatedOn
			,B.TaxCode										--TaxRate_Id
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.TrnCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)			--TransactionTax
			,Columbus.GetMoneyAmountUsingIndicator(B.TrnNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--TransactionGross
			,B.ClubCurrencyId								--ClubCurrency_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.ClubCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--ClubTotalValue
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.ClubCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)		--ClubTax
			,Columbus.GetMoneyAmountUsingIndicator(B.ClubNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--ClubGross
			,B.MembCurrencyId								--MemberCurrency_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.MembCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--MemberTotalValue
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.MembCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)		--MemberTax
			,Columbus.GetMoneyAmountUsingIndicator(B.MembNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--MemberGross
			,B.TrnCurrencyExchangeRate						--ClubToAmountCurrencyExchangeRate
			,B.MemberCurrencyExchangeRate					--ClubToMemberCurrencyExchangeRate
			,B.InstallmentPlanId							--InstalmentPlan_Id
			,DCN.DbCrNoteId									--DebitCreditNote_Id
			,CASE											--LegacyDocumentNumber
			    WHEN B.DebitCreditNoteId IS NOT NULL
			     AND DCN.DbCrNoteId IS NULL THEN
					coalesce(B.DocumentNo,  'UNKNOWN (Missing record Id '+cast(B.DebitCreditNoteId as NVARCHAR(MAX))+' in CCDebitCreditNote)')
				ELSE
					B.DocumentNo
			 END
			,B.DueFromDate									--DueStart
			,B.DueToDate									--DueEnd
			,Columbus.YNToBit(B.DeactiveYN)					--IsInactive
			,B.NoOfUnits									--EnteredTonnage
			,B.Rate											--Rate
			,B.CallPercentage								--CallPercentage
			,IB.Id+@coverIdOffSet							--Brokerage_Id
			,B.SerialNo										--SerialNo
			,I.Id+@coverIdOffSet							--Premium_Id
			,CASE
				WHEN B.CoverROPremiumBrokerId IS NOT NULL OR GLBR.BrokerId IS NOT NULL
					THEN 'B' 
					ELSE 'P' 
			  END											--Discriminator
			,COV.CoverId + @coverIdOffset					--PolicyVersion_Id  
		FROM dbo.UWBillingInfo B
			JOIN dbo.UWCover COV ON B.CoverId = COV.CoverNo AND B.PolicyYearId = COV.PolicyYearId
			JOIN @importedCovers C ON COV.CoverId = C.CoverId
			JOIN @importedRiskObjects IRO ON IRO.CoverROId = B.ROId
			LEFT JOIN @insertedPremiums I ON B.CoverROPremiumId = I.Id
			LEFT JOIN @insertedBrokerages IB ON B.CoverROPremiumBrokerId = IB.Id
			LEFT JOIN CCDebitCreditNote DCN ON DCN.DbCrNoteId = B.DebitCreditNoteId
			LEFT JOIN GLBroker GLBR ON GLBR.BrokerId = B.OrganisationId
		WHERE
			B.CoverROPremiumId IS NOT NULL
		UNION ALL
		-- and billing for premiums by coverage...
		SELECT
			 B.BillingInfoId+@coverIdOffSet					--Id
			,NULL											--PolicyItem_Id
			,B.CoverageId									--CoverageId
			,coalesce(B.OrganisationId,C.MainMemberId)		--Organisation_Id
			,B.TransactionTypeId							--TransactionType_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.TrnCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--TransactionTotalValue
			,B.TrnCurrencyId								--TransactionCurrency_Id
			,B.[Days]										--Days
			,Columbus.YNToBit(B.ReversedYN)					--IsReversed
			,B.ReversedAgainst+@coverIdOffset																							--ReversedAgainstBilling_Id
			,CASE WHEN B.CreatedBy IS NULL THEN 1
				WHEN B.CreatedBy = 0 THEN 1
				ELSE B.CreatedBy 
			END																															--CreatedBy
			,B.CreatedOn									--CreatedOn
			,CASE WHEN B.UpdatedBy IS NULL THEN NULL
				WHEN B.UpdatedBy = 0 THEN 1
				ELSE B.UpdatedBy
			END																															--UpdatedBy
			,B.UpdatedOn									--UpdatedOn
			,B.TaxCode										--TaxRate_Id
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.TrnCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)			--TransactionTax
			,Columbus.GetMoneyAmountUsingIndicator(B.TrnNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--TransactionGross
			,B.ClubCurrencyId								--ClubCurrency_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.ClubCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--ClubTotalValue
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.ClubCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)		--ClubTax
			,Columbus.GetMoneyAmountUsingIndicator(B.ClubNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--ClubGross
			,B.MembCurrencyId								--MemberCurrency_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.MembCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--MemberTotalValue
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.MembCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)		--MemberTax
			,Columbus.GetMoneyAmountUsingIndicator(B.MembNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--MemberGross
			,B.TrnCurrencyExchangeRate						--ClubToAmountCurrencyExchangeRate
			,B.MemberCurrencyExchangeRate					--ClubToMemberCurrencyExchangeRate
			,B.InstallmentPlanId							--InstalmentPlan_Id
			,DCN.DbCrNoteId									--DebitCreditNote_Id
			,CASE											--LegacyDocumentNumber
			    WHEN B.DebitCreditNoteId IS NOT NULL
			     AND DCN.DbCrNoteId IS NULL THEN
					coalesce(B.DocumentNo,  'UNKNOWN (Missing record Id '+cast(B.DebitCreditNoteId as NVARCHAR(MAX))+' in CCDebitCreditNote)')
				ELSE
					B.DocumentNo
			 END
			,B.DueFromDate									--DueStart
			,B.DueToDate									--DueEnd
			,Columbus.YNToBit(B.DeactiveYN)					--IsInactive
			,B.NoOfUnits									--EnteredTonnage
			,B.Rate											--Rate
			,B.CallPercentage								--CallPercentage
			,IB.Id+@coverIdOffSet							--Brokerage_Id
			,B.SerialNo										--SerialNo
			,I.Id+@coverIdOffSet							--Premium_Id
			,CASE
				WHEN B.CoverROPremiumBrokerId IS NOT NULL OR GLBR.BrokerId IS NOT NULL
					THEN 'B' 
					ELSE 'P' 
			  END											--Discriminator
			,COV.CoverId + @coverIdOffset					--PolicyVersion_Id
		FROM dbo.UWBillingInfo B
			JOIN dbo.UWCover COV ON B.CoverId = COV.CoverNo AND B.PolicyYearId = COV.PolicyYearId
			JOIN @importedCovers C ON COV.CoverId = C.CoverId
			LEFT JOIN @insertedPremiums I ON B.CoverROPremiumId = I.Id
			LEFT JOIN @insertedBrokerages IB ON B.CoverROPremiumBrokerId = IB.Id
			LEFT JOIN CCDebitCreditNote DCN ON DCN.DbCrNoteId = B.DebitCreditNoteId
			LEFT JOIN GLBroker GLBR ON GLBR.BrokerId = B.OrganisationId
		WHERE B.ROId IS NULL AND B.CoverROPremiumId IS NOT NULL
		UNION ALL
		-- all manual billings that relate to imported risk objects.
		SELECT
			 B.BillingInfoId+@coverIdOffSet					--Id
			,B.ROId+@coverIdOffSet							--PolicyItem_Id
			,B.CoverageId									--CoverageId
			,coalesce(B.OrganisationId,C.MainMemberId)		--Organisation_Id
			,B.TransactionTypeId							--TransactionType_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.TrnCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--TransactionTotalValue
			,B.TrnCurrencyId								--TransactionCurrency_Id
			,B.[Days]										--Days
			,Columbus.YNToBit(B.ReversedYN)					--IsReversed
			,B.ReversedAgainst+@coverIdOffset																							--ReversedAgainstBilling_Id
			,CASE WHEN B.CreatedBy IS NULL THEN 1
				WHEN B.CreatedBy = 0 THEN 1
				ELSE B.CreatedBy 
			END																															--CreatedBy
			,B.CreatedOn									--CreatedOn
			,CASE WHEN B.UpdatedBy IS NULL THEN NULL
				WHEN B.UpdatedBy = 0 THEN 1
				ELSE B.UpdatedBy
			END																															--UpdatedBy
			,B.UpdatedOn									--UpdatedOn
			,B.TaxCode										--TaxRate_Id
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.TrnCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)			--TransactionTax
			,Columbus.GetMoneyAmountUsingIndicator(B.TrnNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--TransactionGross
			,B.ClubCurrencyId								--ClubCurrency_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.ClubCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--ClubTotalValue
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.ClubCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)		--ClubTax
			,Columbus.GetMoneyAmountUsingIndicator(B.ClubNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--ClubGross
			,B.MembCurrencyId								--MemberCurrency_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.MembCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--MemberTotalValue
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.MembCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)		--MemberTax
			,Columbus.GetMoneyAmountUsingIndicator(B.MembNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--MemberGross
			,B.TrnCurrencyExchangeRate						--ClubToAmountCurrencyExchangeRate
			,B.MemberCurrencyExchangeRate					--ClubToMemberCurrencyExchangeRate
			,B.InstallmentPlanId							--InstalmentPlan_Id
			,DCN.DbCrNoteId									--DebitCreditNote_Id
			,CASE											--LegacyDocumentNumber
			    WHEN B.DebitCreditNoteId IS NOT NULL
			     AND DCN.DbCrNoteId IS NULL THEN
					coalesce(B.DocumentNo,  'UNKNOWN (Missing record Id '+cast(B.DebitCreditNoteId as NVARCHAR(MAX))+' in CCDebitCreditNote)')
				ELSE
					B.DocumentNo
			 END
			,B.DueFromDate									--DueStart
			,B.DueToDate									--DueEnd
			,Columbus.YNToBit(B.DeactiveYN)					--IsInactive
			,B.NoOfUnits									--EnteredTonnage
			,B.Rate											--Rate
			,B.CallPercentage								--CallPercentage
			,IB.Id+@coverIdOffSet							--Brokerage_Id
			,B.SerialNo										--SerialNo
			,NULL											--Premium_Id
			,CASE
				WHEN B.CoverROPremiumBrokerId IS NOT NULL OR GLBR.BrokerId IS NOT NULL
					THEN 'B' 
					ELSE 'P' 
			  END											--Discriminator
			,COV.CoverId + @coverIdOffset					--PolicyVersion_Id
		FROM dbo.UWBillingInfo B
			JOIN dbo.UWCover COV ON B.CoverId = COV.CoverNo AND B.PolicyYearId = COV.PolicyYearId
			JOIN @importedCovers C ON COV.CoverId = C.CoverId
			JOIN @importedRiskObjects IRO ON IRO.CoverROId = B.ROId
			LEFT JOIN @insertedBrokerages IB ON B.CoverROPremiumBrokerId = IB.Id
			LEFT JOIN CCDebitCreditNote DCN ON DCN.DbCrNoteId = B.DebitCreditNoteId
			LEFT JOIN GLBroker GLBR ON GLBR.BrokerId = B.OrganisationId
		WHERE B.CoverROPremiumId IS NULL
		UNION ALL
		-- and all manual billings with no related ROId.
		SELECT
			 B.BillingInfoId+@coverIdOffSet					--Id
			,B.ROId+@coverIdOffSet							--PolicyItem_Id
			,B.CoverageId									--CoverageId
			,coalesce(B.OrganisationId,C.MainMemberId)		--Organisation_Id
			,B.TransactionTypeId							--TransactionType_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.TrnCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--TransactionTotalValue
			,B.TrnCurrencyId								--TransactionCurrency_Id
			,B.[Days]										--Days
			,Columbus.YNToBit(B.ReversedYN)					--IsReversed
			,B.ReversedAgainst+@coverIdOffset																							--ReversedAgainstBilling_Id
			,CASE WHEN B.CreatedBy IS NULL THEN 1
				WHEN B.CreatedBy = 0 THEN 1
				ELSE B.CreatedBy 
			END																															--CreatedBy
			,B.CreatedOn									--CreatedOn
			,CASE WHEN B.UpdatedBy IS NULL THEN NULL
				WHEN B.UpdatedBy = 0 THEN 1
				ELSE B.UpdatedBy
			END																															--UpdatedBy
			,B.UpdatedOn									--UpdatedOn
			,B.TaxCode										--TaxRate_Id
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.TrnCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)			--TransactionTax
			,Columbus.GetMoneyAmountUsingIndicator(B.TrnNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--TransactionGross
			,B.ClubCurrencyId								--ClubCurrency_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.ClubCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--ClubTotalValue
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.ClubCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)		--ClubTax
			,Columbus.GetMoneyAmountUsingIndicator(B.ClubNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--ClubGross
			,B.MembCurrencyId								--MemberCurrency_Id
			,Columbus.GetMoneyAmountUsingIndicator(B.MembCurrencyAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--MemberTotalValue
			,Columbus.GetMoneyAmountUsingIndicator(coalesce(B.MembCurrencyTaxAmt,0), B.CoverROPremiumBrokerId, B.DebitCreditInd)		--MemberTax
			,Columbus.GetMoneyAmountUsingIndicator(B.MembNetPremiumAmt, B.CoverROPremiumBrokerId, B.DebitCreditInd)						--MemberGross
			,B.TrnCurrencyExchangeRate						--ClubToAmountCurrencyExchangeRate
			,B.MemberCurrencyExchangeRate					--ClubToMemberCurrencyExchangeRate
			,B.InstallmentPlanId							--InstalmentPlan_Id
			,DCN.DbCrNoteId									--DebitCreditNote_Id
			,CASE											--LegacyDocumentNumber
			    WHEN B.DebitCreditNoteId IS NOT NULL
			     AND DCN.DbCrNoteId IS NULL THEN
					coalesce(B.DocumentNo,  'UNKNOWN (Missing record Id '+cast(B.DebitCreditNoteId as NVARCHAR(MAX))+' in CCDebitCreditNote)')
				ELSE
					B.DocumentNo
			 END
			,B.DueFromDate									--DueStart
			,B.DueToDate									--DueEnd
			,Columbus.YNToBit(B.DeactiveYN)					--IsInactive
			,B.NoOfUnits									--EnteredTonnage
			,B.Rate											--Rate
			,B.CallPercentage								--CallPercentage
			,IB.Id+@coverIdOffSet							--Brokerage_Id
			,B.SerialNo										--SerialNo
			,NULL											--Premium_Id
			,CASE
				WHEN B.CoverROPremiumBrokerId IS NOT NULL OR GLBR.BrokerId IS NOT NULL
					THEN 'B' 
					ELSE 'P' 
			  END											--Discriminator
			,COV.CoverId + @coverIdOffset					--PolicyVersion_Id
		FROM dbo.UWBillingInfo B
			JOIN dbo.UWCover COV ON B.CoverId = COV.CoverNo AND B.PolicyYearId = COV.PolicyYearId
			JOIN @importedCovers C ON COV.CoverId = C.CoverId
			LEFT JOIN @insertedBrokerages IB ON B.CoverROPremiumBrokerId = IB.Id
			LEFT JOIN CCDebitCreditNote DCN ON DCN.DbCrNoteId = B.DebitCreditNoteId
			LEFT JOIN GLBroker GLBR ON GLBR.BrokerId = B.OrganisationId
		WHERE B.CoverROPremiumId IS NULL
			AND B.ROId IS NULL
	END
	
	-- where reversals refer to non-exitant billings set the ReversedAgainstBilling_Id to null
	
	UPDATE Reversal
	SET Reversal.ReversedAgainstBilling_Id = NULL
	FROM Columbus.Billing Reversal
	LEFT JOIN Columbus.Billing Reversed ON Reversal.ReversedAgainstBilling_Id=Reversed.Id
	WHERE Reversal.ReversedAgainstBilling_Id IS NOT NULL
	  AND Reversed.Id IS NULL
	
	------------------------------------------------------------------------------------------------------
	-- Import Release Calls
	------------------------------------------------------------------------------------------------------
	IF(@importReleaseCalls = 1)
	BEGIN
		EXEC Columbus.WriteLine  'Importing Release Calls'
		
		DECLARE @releaseCallsGrouped TABLE
		(
			MinReleaseCallId INT,
			MaxPolicyYear INT,
			MemberId INT,
			CreatedBy INT,
			CreatedOn DATETIME,
			StatusId NVARCHAR(6),
			IsGuarantee CHAR(1),
			Remarks NVARCHAR(1000)
		)
		
		INSERT INTO @releaseCallsGrouped
		(
			MinReleaseCallId,
			MaxPolicyYear,
			MemberId,
			CreatedBy,
			CreatedOn,
			StatusId,
			IsGuarantee,
			Remarks
		)
		SELECT 
			MIN(RCD.ReleaseCallId) AS MinReleaseCallId,
			MAX(RCD.PolicyYearId) AS MaxPolicyYear,
			C.MemberId,
			RCD.CreatedBy,
			RCD.CreatedOn,
			RCD.StatusId,
			RCD.IsGuarantee,
			UPPER(RCD.Remarks)
		FROM dbo.UWReleaseCallDetails RCD
			JOIN UWCover C ON RCD.CoverNo = C.CoverNo AND RCD.PolicyYearId = C.PolicyYearId
			JOIN @importedCovers IC ON C.CoverId = IC.CoverId
		GROUP BY C.MemberId, RCD.CreatedBy, RCD.CreatedOn, RCD.StatusId, RCD.IsGuarantee, UPPER(RCD.Remarks)
		
		INSERT INTO Columbus.ReleaseCall
		(
			Id,
			NotifiedDate,
			RaisedFor_Id,
			Comments,
			ReleaseCallStatus_Id,
			Member_Id,
			CreatedBy,
			CreatedOn,
			UpdatedBy,
			UpdatedOn
		)
		SELECT 
			RCG.MinReleaseCallId+@coverIdOffset,
			CAST(CAST(RCG.MaxPolicyYear AS varchar) + '-02-20T12:00:00.000' AS DATETIME),
			'ME', -- Member
			RCD.Remarks,
			CASE WHEN RCG.StatusId = 'W' THEN 1 -- Work In Progress -> In Progress
				WHEN RCG.StatusId = 'Q' THEN 1 -- Quoted -> In Progress
				WHEN RCG.StatusId = 'I' AND RCG.IsGuarantee = 'N' THEN 2 -- Issued and not guarantee -> Accepted
				WHEN RCG.StatusId = 'I' AND RCG.IsGuarantee = 'Y' THEN 4 -- Issued and guarantee -> Guarantee Assigned
				WHEN RCG.StatusId = 'C' THEN 3 -- Cancelled -> Rejected (subject to change pending further analysis - could potentially be Reversed)
			END,
			C.MemberId,
			RCG.CreatedBy,
			RCG.CreatedOn,
			NULL,
			NULL
		FROM dbo.UWReleaseCallDetails RCD
			JOIN UWCover C ON RCD.CoverNo = C.CoverNo AND RCD.PolicyYearId = C.PolicyYearId
			JOIN @importedCovers IC ON C.CoverId = IC.CoverId
			JOIN @releaseCallsGrouped RCG ON RCG.MemberId = C.MemberId 
				AND RCG.CreatedBy = RCD.CreatedBy 
				AND RCG.CreatedOn = RCD.CreatedOn 
				AND RCG.StatusId = RCD.StatusId 
				AND RCG.IsGuarantee = RCD.IsGuarantee
				AND (UPPER(RCG.Remarks) = UPPER(RCD.Remarks) OR (RCG.Remarks IS NULL AND RCD.Remarks IS NULL))
				AND RCG.MinReleaseCallId = RCD.ReleaseCallId
		
		EXEC Columbus.WriteLine 'Importing Release Call Policies'
		DECLARE @importedReleaseCallPolicies TABLE
		(
			Id INT,
			PolicyId INT,
			ReleaseCallId INT,
			CreatedBy INT,
			CreatedOn DATETIME
		)
		
		INSERT INTO @importedReleaseCallPolicies
		(
			Id,
			PolicyId,
			ReleaseCallId,
			CreatedBy,
			CreatedOn
		)
		SELECT 
			((ROW_NUMBER() OVER(PARTITION BY RCG.MinReleaseCallId ORDER BY C.CoverId)-1)*100000) + RCG.MinReleaseCallId+@coverIdOffset,
			C.CoverId,
			RCG.MinReleaseCallId+@coverIdOffset,
			RCG.CreatedBy,
			RCG.CreatedOn
		FROM dbo.UWReleaseCallDetails RCD
			JOIN UWCover C ON RCD.CoverNo = C.CoverNo AND RCD.PolicyYearId = C.PolicyYearId
			JOIN @importedCovers IC ON C.CoverId = IC.CoverId
			JOIN @releaseCallsGrouped RCG ON RCG.MemberId = C.MemberId 
				AND RCG.CreatedBy = RCD.CreatedBy 
				AND RCG.CreatedOn = RCD.CreatedOn 
				AND RCG.StatusId = RCD.StatusId 
				AND RCG.IsGuarantee = RCD.IsGuarantee
				AND (UPPER(RCG.Remarks) = UPPER(RCD.Remarks) OR (RCG.Remarks IS NULL AND RCD.Remarks IS NULL))
		GROUP BY
			C.CoverId, 
			RCG.MinReleaseCallId,
			RCG.CreatedBy,
			RCG.CreatedOn
		
		INSERT INTO Columbus.ReleaseCallPolicy
		(
			Id,
			PolicyId,
			ReleaseCall_Id,
			CreatedBy,
			CreatedOn
		)
		SELECT  
			Id,
			PolicyId,
			ReleaseCallId,
			CreatedBy,
			CreatedOn
		FROM    @importedReleaseCallPolicies
		
		EXEC Columbus.WriteLine  'Importing Release Call Policy Items'
		
		INSERT INTO Columbus.ReleaseCallPolicyItem
		(
			Id,
			ReleaseCallPolicy_Id,
			PolicyItem_Id,
			EtpAmount,
			AmountReleased,
			ReleaseCallPercentage,
			CreatedBy,
			CreatedOn,
			UpdatedBy,
			UpdatedOn
		)
		SELECT  
			RCD.ReleaseCallId + @coverIdOffset,
			RCP.Id,
			RCD.CoverROId + @coverIdOffset,
			RCD.ETPAmt,
			RCD.TrnCurrencyAmt,
			RCD.RelCallPercentage,
			RCD.CreatedBy,
			RCD.CreatedOn,
			RCD.UpdatedBy,
			RCD.UpdatedOn
		FROM dbo.UWReleaseCallDetails RCD
			JOIN UWCover C ON RCD.CoverNo = C.CoverNo AND RCD.PolicyYearId = C.PolicyYearId			
			JOIN @releaseCallsGrouped RCG ON C.MemberId = RCG.MemberId
				AND RCD.CreatedBy = RCG.CreatedBy
				AND RCD.CreatedOn = RCG.CreatedOn
				AND RCD.StatusId = RCG.StatusId
				AND RCD.IsGuarantee = RCG.IsGuarantee
				AND (UPPER(RCG.Remarks) = UPPER(RCD.Remarks) OR (RCG.Remarks IS NULL AND RCD.Remarks IS NULL))
			JOIN @importedReleaseCallPolicies RCP ON C.CoverId = RCP.PolicyId 
				AND RCP.ReleaseCallId = RCG.MinReleaseCallId + @coverIdOffset
				
		EXEC Columbus.WriteLine  'Finished Importing Release Calls'
	END
	
	IF(@importLaidUpReturns = 1)
	BEGIN
		INSERT INTO Columbus.LaidUp
		(
			Id,
			NotifiedDate,
			Details,
			RaisedFor_Id,
			TotalAmount,
			TaxAmount,
			CreatedBy,
			CreatedOn,
			UpdatedBy,
			UpdatedOn,
			CoverLaidUp_Id,
			PolicyItem_Id
		)
		SELECT  LURD.LurDetailsId + @coverIdOffset,
			LUR.NotifiedDate,
			'Migrated from Milan',
			COALESCE(LUR.RaisedFor, 'ME'),
			COALESCE(LUR.ReturnTrnCurAmt, 0),
			COALESCE(LUR.TaxTrnCurAmt, 0),
			LUR.CreatedBy,
			LUR.CreatedOn,
			LUR.UpdatedBy,
			LUR.UpdatedOn,
			NULL,
			CRO.CoverROId + @coverIdOffset
		FROM    dbo.UWLUR LUR JOIN dbo.UWLURDetails LURD ON LUR.LURId = LURD.LURId
			JOIN dbo.UWCover C ON C.CoverNo = LURD.CoverNo AND C.PolicyYearId = LURD.PolicyYearId
			JOIN dbo.UWCoverRO CRO ON CRO.CoverId = C.CoverId and CRO.Columbus_PolicyItemType_Id = 1
			JOIN dbo.UWCoverROShip CROS ON CROS.ShipId = LUR.ShipId AND CROS.CoverROShipId = CRO.RODetailsId
			JOIN @importedCovers IC ON C.CoverId = IC.CoverId
			JOIN @importedRiskObjects IRO ON IRO.CoverROId = CRO.CoverROId
			
		INSERT INTO Columbus.LaidUpReturn
		(
			LaidUp_Id,
			MooredDate,
			ClearOfCargoDate,
			CommencedLoadingDate,
			SailingDate,
			RepairsStartDate,
			RepairsEndDate,
			NumberOfCrewOnBoard,
			DidVesselShiftPosition,
			HasUploadedEvidence,
			IsBillable,
			ReturnPercentage,
			ReturnAmount,
			PenaltyPercentage
		)
		SELECT  
			LURD.LurDetailsId + @coverIdOffset,
			[Columbus].[DateOnOrAfterStartOfPolicyYear](LUR.DateMoored, C.PolicyYearId),
			[Columbus].[DateOnOrAfterStartOfPolicyYear](LUR.DateClearOfCargo, C.PolicyYearId),
			[Columbus].[DateOnOrBeforeEndOfPolicyYear](LUR.DateLoading, C.PolicyYearId),
			[Columbus].[DateOnOrBeforeEndOfPolicyYear](LUR.DateSailing, C.PolicyYearId),
			[Columbus].[DateOnOrAfterStartOfPolicyYear](LUR.DateUnderRepairFrom, C.PolicyYearId),
			[Columbus].[DateOnOrBeforeEndOfPolicyYear](LUR.DateUnderRepairTo, C.PolicyYearId),
			COALESCE(LUR.NoOfCrewMembers, 0), -- TODO Ronan: consider making target column nullable instead.
			Columbus.YNToBit(LUR.DidShipShiftPosition),
			Columbus.YNToBit(LUR.UploadEvidence),
			1,
			COALESCE(LURD.ReturnPercentage, 0),
			COALESCE(LURD.ReturnURTrnCurAmt, 0) + COALESCE(LURD.ReturnNURTrnCurAmt, 0),
			LURD.PenaltyPercentage
		FROM    dbo.UWLUR LUR JOIN dbo.UWLURDetails LURD ON LUR.LURId = LURD.LURId
			JOIN dbo.UWCover C ON C.CoverNo = LURD.CoverNo AND C.PolicyYearId = LURD.PolicyYearId
			JOIN dbo.UWCoverRO CRO ON CRO.CoverId = C.CoverId and CRO.Columbus_PolicyItemType_Id = 1
			JOIN dbo.UWCoverROShip CROS ON CROS.ShipId = LUR.ShipId AND CROS.CoverROShipId = CRO.RODetailsId
			JOIN @importedCovers IC ON C.CoverId = IC.CoverId
			JOIN @importedRiskObjects IRO ON IRO.CoverROId = CRO.CoverROId
			
	END
END
GO


-------------------------------------------------------------------------------------------------------------------------------------------
-- StoredProcedure: PostPolicyDataMigration
-------------------------------------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Columbus.PostPolicyDataMigration') AND type in (N'P', N'PC'))
	DROP PROCEDURE Columbus.PostPolicyDataMigration
GO

CREATE PROCEDURE Columbus.PostPolicyDataMigration
(
	@coverId INT,
	@quoteIdOffSet INT,
	@coverIdOffSet INT,
	@isFullImport BIT,
	@importNotifications BIT,
	@updateClaims BIT
)
AS
BEGIN

-----------------------------------------------------------------------------------------------------	
----  Update Notifications
-----------------------------------------------------------------------------------------------------	

	IF (@importNotifications=1 AND @isFullImport=1)
	BEGIN
		EXEC Columbus.WriteLine 'Updating Notifications'
 
		DECLARE @notifications TABLE
		(
			Id			INT,
			QuoteId		INT,
			VersionNo	SMALLINT
		)
			 
		INSERT INTO @notifications
		SELECT 
			Id,	
			cast(substring(OldHyperlink3Url,10,charindex('&',OldHyperlink3Url)-10)  as int) as QuoteId,
			cast(right(OldHyperlink3Url, len(OldHyperlink3Url) - (charindex('&',OldHyperlink3Url)+8)) as SMALLINT) as VersionNo
		FROM Columbus.[Notification]
		WHERE OldHyperlink3Url LIKE 'Quote?Id=%'

	 
  		UPDATE Columbus.[Notification]			
		SET Hyperlink3Url='Policy?Id='+cast(coalesce(P.Id, C.Id) AS NVARCHAR(10)),
			Hyperlink3Text=cast(coalesce(P.Number,C.Number) as NVARCHAR(6))+'/'+cast(coalesce(V.PolicyDateYear,C.PolicyDateYear) AS NVARCHAR(4))
		FROM Columbus.[Notification] N
		JOIN @notifications H ON N.Id=H.Id
		LEFT JOIN Columbus.Policy P ON P.Id=H.QuoteId+@quoteIdOffSet
		LEFT JOIN Columbus.PolicyVersion V ON P.CurrentPolicyVersion_Id=V.Id
		OUTER APPLY
		(
			SELECT TOP 1 
					QCL.CoverId					AS Id, 
					C.CoverNo					AS Number, 
					C.PolicyYearId				AS PolicyDateYear
			FROM dbo.UWQuoteCoverLink QCL
			JOIN dbo.UWCover C ON QCL.CoverId = C.CoverId
			WHERE QCL.QuoteId = H.QuoteId
			ORDER BY QCL.QuoteCoverLinkId
		) C


 		UPDATE Columbus.[Notification]			
		SET Hyperlink3Url='Policy?Id='+substring(OldHyperlink3Url,10,10),
			Hyperlink3Text=OldHyperlink3Text 
		WHERE OldHyperlink3Url LIKE 'Cover?Id=%'
		
	END
	
-----------------------------------------------------------------------------------------------------	
----  Update Blue Card
-----------------------------------------------------------------------------------------------------	
	
	IF(@isFullImport=1)
	BEGIN
		EXEC Columbus.WriteLine 'Updating BlueCards'
	
		UPDATE Columbus.BlueCard
		SET PolicyItem_Id = I.Id
		FROM Columbus.BlueCard BC
		JOIN Columbus.PolicyItem I ON BC.QuotePolicyItem_Id+@quoteIdOffSet=I.Id
	
		UPDATE Columbus.BlueCard
		SET PolicyItem_Id = I.Id
		FROM Columbus.BlueCard BC
		JOIN Columbus.QuoteCoverRoLink QCL ON BC.QuotePolicyItem_Id=QCL.QuoteROId
		JOIN Columbus.PolicyItem I ON QCL.CoverROId+@coverIdOffSet=I.Id
	
-----------------------------------------------------------------------------------------------------	
----  Update Letter of Undertaking
-----------------------------------------------------------------------------------------------------	
	
		EXEC Columbus.WriteLine 'Updating Letters of Undertaking'		
		
		UPDATE Columbus.LetterOfUndertaking
		SET PolicyItem_Id = I.Id
		FROM Columbus.LetterOfUndertaking LoU
		JOIN Columbus.PolicyItem I ON LoU.QuotePolicyItem_Id+@quoteIdOffSet=I.Id
	
		UPDATE Columbus.LetterOfUndertaking
		SET PolicyItem_Id = I.Id
		FROM Columbus.LetterOfUndertaking LoU
		JOIN Columbus.QuoteCoverRoLink QCL ON LoU.QuotePolicyItem_Id=QCL.QuoteROId
		JOIN Columbus.PolicyItem I ON QCL.CoverROId+@coverIdOffSet=I.Id
	
-----------------------------------------------------------------------------------------------------	
----  Update CLC
-----------------------------------------------------------------------------------------------------	
	
		EXEC Columbus.WriteLine 'Updating CLC'
		
		UPDATE BC SET
			Columbus_PolicyItem_Id = ISNULL(quoteMigratedItem.Id, coverMigratedItem.Id)
			, Columbus_Policy_Id = PV.Policy_Id
		FROM    dbo.UWBlueCard BC
			LEFT JOIN Columbus.PolicyItem quoteMigratedItem ON quoteMigratedItem.Id = BC.Columbus_UWQuoteCover_PolicyItem_Id + @quoteIdOffSet AND quoteMigratedItem.Ship_Id = BC.ShipId
			LEFT JOIN Columbus.PolicyItem coverMigratedItem ON coverMigratedItem.Id = BC.Columbus_UWQuoteCover_PolicyItem_Id + @coverIdOffset AND coverMigratedItem.Ship_Id = BC.ShipId
			LEFT JOIN Columbus.PolicyVersion PV ON quoteMigratedItem.PolicyVersion_Id = PV.Id OR coverMigratedItem.PolicyVersion_Id = PV.Id
	
-----------------------------------------------------------------------------------------------------	
----  Update BBC
-----------------------------------------------------------------------------------------------------	
	
		EXEC Columbus.WriteLine 'Updating BBC'
		
		UPDATE BC SET
			Columbus_PolicyItem_Id = ISNULL(quoteMigratedItem.Id, coverMigratedItem.Id)
			, Columbus_Policy_Id = PV.Policy_Id
		FROM    dbo.UWBunkerCertificate BC
			LEFT JOIN Columbus.PolicyItem quoteMigratedItem ON quoteMigratedItem.Id = BC.Columbus_UWQuoteCover_PolicyItem_Id + @quoteIdOffset AND quoteMigratedItem.Ship_Id = BC.ShipId
			LEFT JOIN Columbus.PolicyItem coverMigratedItem ON coverMigratedItem.Id = BC.Columbus_UWQuoteCover_PolicyItem_Id + @coverIdOffset AND coverMigratedItem.Ship_Id = BC.ShipId
			LEFT JOIN Columbus.PolicyVersion PV ON quoteMigratedItem.PolicyVersion_Id = PV.Id OR coverMigratedItem.PolicyVersion_Id = PV.Id
	
-----------------------------------------------------------------------------------------------------	
----  Update COFR
-----------------------------------------------------------------------------------------------------	
	
		EXEC Columbus.WriteLine 'Updating COFR'
		
		UPDATE C SET
			Columbus_PolicyItem_Id = ISNULL(quoteMigratedItem.Id, coverMigratedItem.Id)
			, Columbus_Policy_Id = PV.Policy_Id
		FROM    dbo.UWCofr C
			LEFT JOIN Columbus.PolicyItem quoteMigratedItem ON quoteMigratedItem.Id = C.Columbus_UWQuoteCover_PolicyItem_Id + @quoteIdOffset AND quoteMigratedItem.Ship_Id = C.ShipId
			LEFT JOIN Columbus.PolicyItem coverMigratedItem ON coverMigratedItem.Id = C.Columbus_UWQuoteCover_PolicyItem_Id + @coverIdOffset AND coverMigratedItem.Ship_Id = C.ShipId
			LEFT JOIN Columbus.PolicyVersion PV ON quoteMigratedItem.PolicyVersion_Id = PV.Id OR coverMigratedItem.PolicyVersion_Id = PV.Id
			
-----------------------------------------------------------------------------------------------------	
----  Update ITOPF
-----------------------------------------------------------------------------------------------------	
	
		EXEC Columbus.WriteLine 'Updating ITOPF'
		
		UPDATE I SET
			Columbus_PolicyItem_Id = ISNULL(quoteMigratedItem.Id, coverMigratedItem.Id)
			, Columbus_Policy_Id = PV.Policy_Id
		FROM    dbo.UWITOPF I
			LEFT JOIN Columbus.PolicyItem quoteMigratedItem ON quoteMigratedItem.Id = I.Columbus_UWQuoteCover_PolicyItem_Id + @quoteIdOffset AND quoteMigratedItem.Ship_Id = I.ShipId
			LEFT JOIN Columbus.PolicyItem coverMigratedItem ON coverMigratedItem.Id = I.Columbus_UWQuoteCover_PolicyItem_Id + @coverIdOffset AND coverMigratedItem.Ship_Id = I.ShipId
			LEFT JOIN Columbus.PolicyVersion PV ON quoteMigratedItem.PolicyVersion_Id = PV.Id OR coverMigratedItem.PolicyVersion_Id = PV.Id
			
-----------------------------------------------------------------------------------------------------	
----  Update foreign keys for existing amendment records
-----------------------------------------------------------------------------------------------------
		
		EXEC Columbus.WriteLine 'Updating Amendment PolicyItems'
	
		UPDATE MTAItem
		SET MTAItem.CoverPolicyItem_Id=CoverItem.Id
		FROM Columbus.PolicyItem MTAItem
		JOIN Columbus.PolicyItem CoverItem ON MTAItem.UWCoverROPolicyItemId+@coverIdOffSet = CoverItem.Id
			
		EXEC Columbus.WriteLine 'Updating Amendment CoAssured'
	
		UPDATE MTACo
		SET MTACo.CoverCoAssured_Id=CoverCo.Id
		FROM Columbus.CoAssured MTACo
		JOIN Columbus.CoAssured CoverCo ON MTACo.UWCoverCoAssuredId+@coverIdOffSet = CoverCo.Id
		
		EXEC Columbus.WriteLine 'Updating Amendment JointEntrants'
	
		UPDATE MTAJE
		SET MTAJE.CoverJointEntrant_Id=CoverJE.Id
		FROM Columbus.JointEntrant MTAJE
		JOIN Columbus.JointEntrant CoverJE ON MTAJE.UWCoverJointEntrantId+@coverIdOffSet = CoverJE.Id		
		
		EXEC Columbus.WriteLine 'Updating Amendment Mortgagees'
	
		UPDATE MTAMort
		SET MTAMort.CoverMortgagee_Id=CoverMort.Id
		FROM Columbus.Mortgagee MTAMort
		JOIN Columbus.Mortgagee CoverMort ON MTAMort.UWCoverMortgageeId+@coverIdOffSet = CoverMort.Id	
		
		EXEC Columbus.WriteLine 'Updating Amendment GXLs'
		
		UPDATE MTAGxl
		SET MTAGxl.CoverPolicyItemGxlCategory_Id=CoverGxl.Id
		FROM Columbus.PolicyItemGxlCategory MTAGxl
		JOIN Columbus.PolicyItemGxlCategory CoverGxl ON MTAGxl.OldCoverPolicyItemGxlCategoryId+@coverIdOffSet = CoverGxl.Id
		WHERE MTAGxl.Id < @quoteIdOffSet
		
		EXEC Columbus.WriteLine 'Updating Amendment Crew & Officers'
		
		UPDATE MTACrew
		SET MTACrew.CoverPolicyItemCompany_Id=coalesce(ROCrew.Columbus_Migrated_Id, ROOfficer.Columbus_Migrated_Id)
		FROM Columbus.PolicyItemCompany MTACrew
		LEFT JOIN dbo.UWCoverROShipCrewDetails ROCrew ON MTACrew.UWCoverCompanyId = ROCrew.CoverROShipCrewId AND MTACrew.[Type]='C'
		LEFT JOIN dbo.UWCoverROShipOfficerDetails ROOfficer ON ABS(MTACrew.UWCoverCompanyId) = ROOfficer.CoverROShipOfficerId AND MTACrew.[Type]='O'
		
		EXEC Columbus.WriteLine 'Updating Amendment Coverages'
	
		UPDATE MTACoverage
		SET MTACoverage.CoverCoverage_Id=CoverCoverage.Id
		FROM Columbus.Coverage MTACoverage
		JOIN Columbus.Coverage CoverCoverage ON MTACoverage.UWCoverCoverageId+@coverIdOffSet = CoverCoverage.Id	
		
		EXEC Columbus.WriteLine 'Updating Amendment Rules'
	
		UPDATE MTARule
		SET MTARule.CoverRule_Id=CoverRule.Id
		FROM Columbus.[Rule] MTARule
		JOIN Columbus.[Rule] CoverRule ON MTARule.UWCoverRuleId+@coverIdOffSet = CoverRule.Id	

		EXEC Columbus.WriteLine 'Updating Amendment Clauses'
	
		UPDATE MTAClause
		SET MTAClause.CoverClause_Id=CoverClause.Id
		FROM Columbus.Clause MTAClause
		JOIN Columbus.Clause CoverClause ON MTAClause.UWCoverClauseId+@coverIdOffSet = CoverClause.Id	

		EXEC Columbus.WriteLine 'Updating Amendment Premiums'
	
		UPDATE MTAPremium
		SET MTAPremium.CoverPremium_Id=CoverPremium.Id
		FROM Columbus.Premium MTAPremium
		JOIN Columbus.Premium CoverPremium ON MTAPremium.UWCoverPremiumId+@coverIdOffSet = CoverPremium.Id	

		EXEC Columbus.WriteLine 'Updating Amendment Brokerage'
	
		UPDATE MTABrokerage
		SET MTABrokerage.CoverBrokerage_Id=CoverBrokerage.Id
		FROM Columbus.Brokerage MTABrokerage
		JOIN Columbus.Brokerage CoverBrokerage ON MTABrokerage.UWCoverBrokerageId+@coverIdOffSet = CoverBrokerage.Id	
	END
	
-----------------------------------------------------------------------------------------------------	
----  Update ContractFacultativeCover
-----------------------------------------------------------------------------------------------------	
	
	EXEC Columbus.WriteLine 'Updating RI ContractFacultativeCover'
	
	UPDATE CFC
	SET CFC.CoverId = PV.Id
	FROM Columbus.ContractFacultativeCover CFC
	JOIN Columbus.PolicyVersion PV ON CFC.UWCoverId+@coverIdOffSet = PV.Id
	
-----------------------------------------------------------------------------------------------------	
----  Update LineItems
-----------------------------------------------------------------------------------------------------	

	EXEC Columbus.WriteLine 'Updating Line Items'

	UPDATE Li
	SET Li.Billing_Id=Bill.Id
	FROM Columbus.LineItem Li
	JOIN Columbus.Billing Bill ON Li.CoverBilling_Id+@coverIdOffSet = Bill.Id
	
-----------------------------------------------------------------------------------------------------	
----  Update Zodiac
-----------------------------------------------------------------------------------------------------	

	EXEC Columbus.WriteLine 'Updating Zodiac'

	UPDATE Zod
	SET Zod.PolicyVersion_Id=Ver.Id
	FROM Columbus.Zodiac Zod
	JOIN Columbus.PolicyVersion Ver ON Zod.CoverId+@coverIdOffSet = Ver.Id	
	
-----------------------------------------------------------------------------------------------------	
----  Update ZodiacPremiumBilling
-----------------------------------------------------------------------------------------------------	

	EXEC Columbus.WriteLine 'Updating ZodiacPremiumBilling'

	UPDATE Zod
	SET Zod.PremiumBilling_Id=Bill.Id
	FROM Columbus.ZodiacPremiumBilling Zod
	JOIN Columbus.Billing Bill ON Zod.CoverPremiumBilling_Id+@coverIdOffSet = Bill.Id
	
-----------------------------------------------------------------------------------------------------	
----  Update CCDebitCreditNoteDetails
-----------------------------------------------------------------------------------------------------	

	EXEC Columbus.WriteLine 'Updating CCDebitCreditNoteDetails'

	UPDATE dbo.CCDebitCreditNoteDetails
	SET BillingInfoId = BillingInfoId+@coverIdOffSet
	
-----------------------------------------------------------------------------------------------------	
----  Update CLClaim
-----------------------------------------------------------------------------------------------------	

	EXEC Columbus.WriteLine 'Updating CLClaim'

	UPDATE dbo.CLClaim
	SET CoverRoId = CoverRoId+@coverIdOffSet
	WHERE CoverRoId IS NOT NULL
		
-----------------------------------------------------------------------------------------------------	
----  Update Identity table
-----------------------------------------------------------------------------------------------------	

	EXEC Columbus.WriteLine 'Updating Identity Table'
	
	DECLARE @newid INT
	SET @newid=400000000
	
	UPDATE Columbus.[Identity]
	SET Id=(@newId/(LoValue+1))+1
	WHERE Entity IN
	(
		'Approval',
		'Billing',
		'Brokerage',
		'CertificateOfEntry',
		'Clause',
		'CoAssured',
		'Coverage',
		'CoverageGroup',
		'JointEntrant',
		'Mortgagee',
		'Policy',
		'PolicyItem',
		'PolicyItemCompany',
		'PolicyItemGxlCategory',
		'PolicyVersion',
		'PolicyVersionAction',
		'Premium',
		'PremiumHistory',
		'ReleaseCall',
		'ReleaseCallPolicy',
		'ReleaseCallPolicyItem',
		'Rule'
	)

END
GO

-------------------------------------------------------------------------------------------------------------------------------------------
-- StoredProcedure: CertificatePolicyDataMigration
-------------------------------------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Columbus.CertificateDataMigration') AND type in (N'P', N'PC'))
	DROP PROCEDURE Columbus.CertificateDataMigration
GO

CREATE PROCEDURE Columbus.CertificateDataMigration
(
	@coverId INT,
	@quoteId INT,
	@importQuotes BIT,
	@importCovers BIT,
	@coverIdOffset INT,
	@quoteIdOffset INT
) AS
BEGIN

-------------------------------------------------------------------------------------------
-- Import Certificates
-------------------------------------------------------------------------------------------

	EXEC Columbus.WriteLine 'Importing CertificateOfEntry'
	
	DECLARE @importedCoes TABLE
	(
		NewCoeId	INT NOT NULL,
		PRIMARY KEY CLUSTERED (NewCoeId)
	)

	INSERT INTO [Columbus].[CertificateOfEntry]
    (
		Id
       ,SerialNumber
       ,Scope
       ,EffectiveDate
       ,CreatedBy
       ,CreatedOn
       ,UpdatedBy
       ,UpdatedOn
       ,TypeGroup
       ,Member_Id
       ,CertificateDocumentId
       ,EndorsementDocumentId
       ,Approval_Id
       ,Origin
       ,CertificateOutlineId
       ,EndorsementOutlineId
    )
    OUTPUT INSERTED.Id INTO @importedCoes (NewCoeId)
    SELECT
     	CASE
     		WHEN COE.RefType='Q' 
     			THEN COE.CoeId+@quoteIdOffset
     			ELSE COE.CoeId+@coverIdOffset
     	END											--Id
       ,COE.CoeNo									--SerialNumber
       ,COE.CertGenById								--Scope
       ,COE.EffectiveDate							--EffectiveDate
       ,COE.CreatedBy								--CreatedBy
       ,COE.CreatedOn								--CreatedOn
       ,COE.UpdatedBy								--UpdatedBy
       ,COE.UpdatedOn								--UpdatedOn
       ,coalesce(COE.Columbus_TypeGroup,nchar(0))	--TypeGroup
       ,COE.CertificateMemberId						--Member_Id
       ,COE.CertDocumentId							--CertificateDocumentId
       ,COE.EndoDocumentId							--EndorsementDocumentId
       ,COE.Columbus_CurrentApproval_Id				--Approval_Id
       ,CASE
			WHEN COE.CertDocumentId IS NOT NULL	
			THEN 'C'
			ELSE 'E'
		END											--Origin
       ,NULL										--CertificateOutlineId, don't need this as only used for draft certs
       ,NULL										--EndorsementOutlineId, don't need this asonly used for draft endos
    FROM dbo.UWCoe COE
    LEFT JOIN Columbus.Approval A ON COE.Columbus_CurrentApproval_Id = A.Id
    LEFT JOIN.Columbus.ApprovalState AST ON A.ToApprovalState_Id = AST.Id
    WHERE (COE.Columbus_CurrentApproval_Id IS NULL AND COE.ApprovalStatusId IN ('A','R'))
       OR AST.Name IN ('Approved','Rejected')

-------------------------------------------------------------------------------------------
-- Creating Approvals for Milan Certificates
-----------------------------------------------------------------------------------------          

    EXEC Columbus.WriteLine 'Creating Approvals for Milan Certificates'
    
    -- use of table var is performance improvement
    DECLARE @milanApproval TABLE
    (
		NewCoeId			INT NOT NULL,
		ApprovalStatusId	NCHAR(1) NOT NULL,
		UpdatedBy			INT NOT NULL,
		UpdatedOn			DATETIME NOT NULL
	)
	
	INSERT INTO @milanApproval
	SELECT	NewCoe.Id, OldCoe.ApprovalStatusId, OldCoe.UpdatedBy, OldCoe.UpdatedOn
	  FROM Columbus.CertificateOfEntry NewCoe
	  JOIN dbo.UWCoe OldCoe 
	    ON OldCoe.RefType='Q' AND NewCoe.Id = OldCoe.CoeId+@quoteIdOffset
	 WHERE NewCoe.Approval_Id IS NULL

  
  UNION ALL  
  
  	SELECT	NewCoe.Id, OldCoe.ApprovalStatusId, OldCoe.UpdatedBy, OldCoe.UpdatedOn
	  FROM Columbus.CertificateOfEntry NewCoe
	  JOIN dbo.UWCoe OldCoe 
	    ON OldCoe.RefType IN ('C','A') AND NewCoe.Id = OldCoe.CoeId+@coverIdOffset
	 WHERE NewCoe.Approval_Id IS NULL 		


	INSERT INTO Columbus.Approval
	(
		Id,
		CertificateOfEntry_Id,
		FromApprovalState_Id,
		ToApprovalState_Id,
		Comment,
		ActingUser_Id,
		ApproverUser_Id,
		CreatedOn,
		CreatedBy,
		IsRedLineCandidate
	)
	SELECT
		NewCoeId,
		NewCoeId,
		null,
		CASE
			WHEN ApprovalStatusId='A' THEN 4
			WHEN ApprovalStatusId='R' THEN 5
			ELSE null
		END,
		'Approval transfered from Milan',
		UpdatedBy,
		UpdatedBy,
		UpdatedOn,
		UpdatedBy,
		0
	FROM @milanApproval
	  
	UPDATE Coe
	SET	 Coe.Approval_Id=A.Id 
	FROM Columbus.CertificateOfEntry Coe
	JOIN Columbus.Approval A ON Coe.Id = A.CertificateOfEntry_Id
	WHERE Coe.Id >= @quoteIdOffset
	  AND Coe.Approval_Id IS NULL

-------------------------------------------------------------------------------------------
-- Import into CertificateOfEntry_PolicyItem
-----------------------------------------------------------------------------------------          

    EXEC Columbus.WriteLine 'Importing CertificateOfEntry_PolicyItem'
    
    
    INSERT INTO Columbus.CertificateOfEntry_PolicyItem
    (
		CertificateOfEntry_Id,
		PolicyItem_Id
	)
	-- covers
	SELECT	ICoe.NewCoeId,
			Item.Id
	FROM	dbo.UWCoeDetails CoeDet
	JOIN	dbo.UWCoe Coe ON CoeDet.CoeId=Coe.CoeId AND Coe.RefType IN ('C','A')
	JOIN	@importedCoes ICoe ON ICoe.NewCoeId = CoeDet.CoeId+@coverIdOffset
	JOIN	Columbus.PolicyItem Item ON Item.Id = CoeDet.QuoteCoverROId+@coverIdOffset
	
	UNION ALL
	
	-- quotes
	SELECT	ICoe.NewCoeId,
			Item.Id
	FROM	dbo.UWCoeDetails CoeDet
	JOIN	dbo.UWCoe Coe ON CoeDet.CoeId=Coe.CoeId AND Coe.RefType='Q'
	JOIN	@importedCoes ICoe ON ICoe.NewCoeId = CoeDet.CoeId+@quoteIdOffset
	JOIN	Columbus.PolicyItem Item ON Item.Id = CoeDet.QuoteCoverROId+@quoteIdOffset
	
	UNION ALL
	
	 -- bound quotes where risk object not in QuoteCoverROLink (find by matching ShipId)
	 SELECT	ICoe.NewCoeId, MIN(RO.CoverROId)+@coverIdOffset
     FROM   dbo.UWCoe Coe
     JOIN	@importedCoes ICoe ON ICoe.NewCoeId = Coe.CoeId+@quoteIdOffset
     JOIN	dbo.UWCoeDetails CoeDet ON Coe.CoeId=CoeDet.CoeId 
     JOIN	dbo.UWQuote Q ON Q.QuoteId=Coe.QuoteCoverId AND Q.VersionNo=Coe.VersionNo AND Coe.RefType='Q'
     JOIN	dbo.UWQuoteCoverLink QCL ON Q.QuoteId=QCL.QuoteId AND Q.VersionNo=QCL.VersionNo
     JOIN	dbo.UWCoverRO RO ON QCL.CoverId=RO.CoverId
     JOIN	dbo.UWCoverROShip ROSH ON RO.RODetailsId=ROSH.CoverROShipId
     JOIN	Columbus.PolicyItem Item ON Item.Id = RO.CoverROId+@coverIdOffset
    WHERE	Q.StatusId = 'B'
      AND   ROSH.ShipId = CoeDet.ShipId
 GROUP BY	ICoe.NewCoeId, CoeDet.ShipId
 
-------------------------------------------------------------------------------------------
-- Update CertificateOfEntryChecklistAnswer
-------------------------------------------------------------------------------------------

	EXEC Columbus.WriteLine 'Updating CertificateOfEntryChecklistAnswer'
	
	UPDATE Answer
	SET Answer.CertificateOfEntry_Id = Answer.CertificateOfEntry_Id + 
			CASE
				WHEN Coe.RefType='Q' 
					THEN @quoteIdOffset
				    ELSE @coverIdOffset
			END
	FROM Columbus.CertificateOfEntryChecklistAnswer Answer
	JOIN dbo.UWCoe Coe ON Answer.CertificateOfEntry_Id=Coe.CoeId
	
	IF EXISTS (SELECT 1 FROM sys.tables WHERE name='CertificateOfEntryChecklistAnswer_ForNotMigratedCerts' AND schema_id=schema_id('Legacy'))
	BEGIN
		DROP TABLE Legacy.CertificateOfEntryChecklistAnswer_ForNotMigratedCerts
	END
		
	CREATE TABLE Legacy.CertificateOfEntryChecklistAnswer_ForNotMigratedCerts
	(
		[Id] [int] NOT NULL,
		[IsChecked] [bit] NOT NULL,
		[CreatedBy] [int] NOT NULL,
		[CreatedOn] [datetime] NOT NULL,
		[UpdatedBy] [int] NULL,
		[UpdatedOn] [datetime] NULL,
		[CertificateOfEntry_Id] [int] NOT NULL,
		[CertificateOfEntryChecklistQuestion_Id] [int] NOT NULL
	)
	
	INSERT INTO Legacy.CertificateOfEntryChecklistAnswer_ForNotMigratedCerts
	SELECT    A.Id, A.IsChecked, A.CreatedBy, A.CreatedOn, A.UpdatedBy, A.UpdatedOn, A.CertificateOfEntry_Id, A.CertificateOfEntryChecklistQuestion_Id
	FROM      Columbus.CertificateOfEntryChecklistAnswer A
	LEFT JOIN Columbus.CertificateOfEntry C ON A.CertificateOfEntry_Id=C.Id
	WHERE C.Id IS NULL

	DELETE FROM A
	FROM   Columbus.CertificateOfEntryChecklistAnswer A
	JOIN   Legacy.CertificateOfEntryChecklistAnswer_ForNotMigratedCerts X
	  ON	A.Id=X.Id	
 
-------------------------------------------------------------------------------------------
-- Update Approval
-------------------------------------------------------------------------------------------

	EXEC Columbus.WriteLine 'Updating Approval'
	
	UPDATE Approval
	SET Approval.CertificateOfEntry_Id = Approval.CertificateOfEntry_Id + 
			CASE
				WHEN Coe.RefType='Q' 
					THEN @quoteIdOffset
				    ELSE @coverIdOffset
			END
	FROM Columbus.Approval Approval
	JOIN dbo.UWCoe Coe ON Approval.CertificateOfEntry_Id=Coe.CoeId 
	
	IF EXISTS (SELECT 1 FROM sys.tables WHERE name='Approval_ForNotMigratedCerts' AND schema_id=schema_id('Legacy'))
	BEGIN
		DROP TABLE Legacy.Approval_ForNotMigratedCerts
	END

	CREATE TABLE Legacy.Approval_ForNotMigratedCerts
	(
		[Id] [int] NOT NULL,
		[CertificateOfEntry_Id] [dbo].[udtTrnId] NOT NULL,
		[FromApprovalState_Id] [int] NULL,
		[ToApprovalState_Id] [int] NOT NULL,
		[Comment] [nvarchar](max) NULL,
		[ActingUser_Id] [int] NULL,
		[ApproverUser_Id] [int] NULL,
		[CreatedOn] [datetime] NOT NULL,
		[CreatedBy] [int] NOT NULL,
		[UpdatedOn] [datetime] NULL,
		[UpdatedBy] [int] NULL,
		[IsRedLineCandidate] [bit] NOT NULL	
	)	  
	  
	INSERT INTO Legacy.Approval_ForNotMigratedCerts
	SELECT    A.Id, A.CertificateOfEntry_Id, A.FromApprovalState_Id, A.ToApprovalState_Id,A.Comment, 
	          A.ActingUser_Id, A.ApproverUser_Id, A.CreatedOn, A.CreatedBy, A.UpdatedOn, A.UpdatedBy, 
	          A.IsRedLineCandidate
	FROM      Columbus.Approval A
	LEFT JOIN Columbus.CertificateOfEntry C ON A.CertificateOfEntry_Id=C.Id
	WHERE C.Id IS NULL	  
	
	DELETE FROM A
	FROM   Columbus.Approval A
	JOIN   Legacy.Approval_ForNotMigratedCerts X
	  ON	A.Id=X.Id	
 
END
GO

-------------------------------------------------------------------------------------------------------------------------------------------
-- StoredProcedure: PolicyDataMigration
-------------------------------------------------------------------------------------------------------------------------------------------

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Columbus.PolicyDataMigration') AND type in (N'P', N'PC'))
	DROP PROCEDURE Columbus.PolicyDataMigration
GO

CREATE PROCEDURE Columbus.PolicyDataMigration
(
	@coverId INT = NULL,
	@quoteId INT = NULL,
	@importQuotes BIT = 1,
	@importCovers BIT = 1,
	@importCoverageGroups BIT = 0,
	@importCoverages BIT = 0,
	@importRules BIT = 0,
	@importClauses BIT = 0,
	@importPolicyItems BIT = 0,
	@importJointEntrants BIT = 0,
	@importCoAssureds BIT = 0,
	@importMortgagees BIT = 0,
	@importGxlCategories BIT = 0,
	@importPremiums BIT = 0,
	@importBrokerages BIT = 0,
	@importPremiumHistories BIT = 0,
	@importCrews BIT = 0,
	@importOfficers BIT = 0,
	@importPolicyVersionActions BIT = 0,
	@importBilling BIT = 0,
	@importNotifications BIT = 0,
	@importCertificates BIT = 0,
	@updateClaims BIT = 0,
	@importReleaseCalls BIT = 0,
	@importLaidUpReturns BIT = 0
)
AS
BEGIN
	DECLARE @quoteIdOffSet INT
	SET @quoteIdOffSet = 100000000
	
	DECLARE @coverIdOffSet INT
	SET @coverIdOffSet = 200000000

	DECLARE @pandiBusinessClassOffset INT
	SET @pandiBusinessClassOffset = 1000000

	DECLARE @defenceBusinessClassIdOffset INT
	SET @defenceBusinessClassIdOffset = 2000000

	DECLARE @crewFactor INT
	SET @crewFactor = 100000

	DECLARE @officerIdOffset INT
	SET @officerIdOffset = 10000000

	IF(@importClauses = 1)
	BEGIN
		SET @importRules = 1
	END
	IF(@importBilling = 1)
	BEGIN
		SET @importBrokerages = 1
	END
	IF(@importBrokerages = 1 OR @importPremiumHistories = 1)
	BEGIN
		SET @importPremiums = 1
	END
	IF(@importCoverages = 1 
		OR @importPremiums = 1 
		OR @importJointEntrants = 1 
		OR @importCoAssureds = 1 
		OR @importMortgagees = 1 
		OR @importGxlCategories = 1 
		OR @importCrews = 1 
		OR @importOfficers = 1 
		OR @importCertificates = 1 
		OR @importReleaseCalls = 1
		OR @importLaidUpReturns = 1)
	BEGIN
		SET @importPolicyItems = 1
	END	
	IF(@importRules = 1 OR @importPremiums = 1)
	BEGIN
		SET @importCoverages = 1
	END
	IF(@importPolicyItems = 1 OR @importCoverages = 1)
	BEGIN
		SET @importCoverageGroups = 1
	END


	
	DECLARE @isFullImport BIT
	
	IF (@importQuotes=1 AND @importCovers=1 AND @quoteId IS NULL AND @coverId IS NULL)
	BEGIN
		SET @isFullImport = 1
	END		
	ELSE
	BEGIN
		SET @isFullImport = 0
	END		
	
	IF (@importQuotes = 1)
	BEGIN
		EXEC Columbus.QuotePolicyDataMigration
			@quoteIdOffSet = @quoteIdOffSet,
			@pandiBusinessClassOffset = @pandiBusinessClassOffset,
			@defenceBusinessClassIdOffset = @defenceBusinessClassIdOffset,
			@crewFactor = @crewFactor,
			@officerIdOffset = @officerIdOffset,
			@quoteId = @quoteId,
			@importCoverageGroups = @importCoverageGroups,
			@importCoverages = @importCoverages,
			@importRules = @importRules,
			@importClauses = @importClauses,
			@importPolicyItems = @importPolicyItems,
			@importJointEntrants = @importJointEntrants,
			@importCoAssureds = @importCoAssureds,
			@importMortgagees = @importMortgagees,
			@importGxlCategories = @importGxlCategories,
			@importPremiums = @importPremiums,
			@importBrokerages = @importBrokerages,
			@importPremiumHistories = @importPremiumHistories,
			@importCrews = @importCrews,
			@importOfficers = @importOfficers,
			@importPolicyVersionActions = @importPolicyVersionActions,
			@importBilling = @importBilling
	END

	IF (@importCovers=1)
	BEGIN
		EXEC Columbus.CoverPolicyDataMigration
			@coverIdOffSet = @coverIdOffSet,
			@pandiBusinessClassOffset = @pandiBusinessClassOffset,
			@defenceBusinessClassIdOffset = @defenceBusinessClassIdOffset,
			@crewFactor = @crewFactor,
			@officerIdOffset = @officerIdOffset,
			@coverId = @coverId,
			@importCoverageGroups = @importCoverageGroups,
			@importCoverages = @importCoverages,
			@importRules = @importRules,
			@importClauses = @importClauses,
			@importPolicyItems = @importPolicyItems,
			@importJointEntrants = @importJointEntrants,
			@importCoAssureds = @importCoAssureds,
			@importMortgagees = @importMortgagees,
			@importGxlCategories = @importGxlCategories,
			@importPremiums = @importPremiums,
			@importBrokerages = @importBrokerages,
			@importPremiumHistories = @importPremiumHistories,
			@importCrews = @importCrews,
			@importOfficers = @importOfficers,
			@importPolicyVersionActions = @importPolicyVersionActions,
			@importBilling = @importBilling,
			@importReleaseCalls = @importReleaseCalls,
			@importLaidUpReturns = @importLaidUpReturns
	END		
	
	IF (@importCertificates = 1)
	BEGIN
		EXEC Columbus.CertificateDataMigration
			@importQuotes = @importQuotes,
			@importCovers = @importCovers,
			@quoteId = @quoteId,
			@coverId = @coverId,
			@quoteIdOffSet = @quoteIdOffset,
			@coverIdOffSet = @coverIdOffSet 
	END

	EXEC Columbus.PostPolicyDataMigration
		@coverId = @coverId,
		@quoteIdOffSet = @quoteIdOffSet,
		@coverIdOffSet = @coverIdOffSet,
		@isFullImport = @isFullImport,
		@importNotifications = @importNotifications,
		@updateClaims = @updateClaims	

END
GO